SQL Inception

SELECT CONCAT('SQL', ' ', 'Inception');

Courtesy of Sergej Veliseičik

Hello, I'm Marius Balčytis

Problem

You need to run SQL migration script

Example

id	price	status
1	10.00	active
2	12.00	inactive
3	24.00	inactive
4	43.00	active
5	51.00	inactive

Double the price and activate inactive products

Solution

            UPDATE prices
                SET price = price * 2, status = 'active'
                WHERE status = 'inactive';

Problem

You run it
Oops! Triple the prices
Which of them were changed?

Solution

Backup the table before running

Problem

You backup the table before running
Someone inserts new record
Someone updates old one
You run update query

Solution

            UPDATE prices SET price = price * 2, status = 'active'
    WHERE id = 2;
            UPDATE prices SET price = price * 2, status = 'active'
    WHERE id = 3;
            UPDATE prices SET price = price * 2, status = 'active'
    WHERE id = 5;

How?

Select needed rows, use reg-exp magic
Use script - select and generate SQLs
Use SQL Inception!

Select needed rows, review

            SELECT *, price * 2 FROM prices WHERE status = 'inactive';

Select SQL statements

            SELECT CONCAT(
                    'UPDATE prices SET price = ',
                    price * 2,
                    ', status = "active" WHERE id = ', id,
                    ';'
                )
                FROM prices WHERE status = 'inactive';

Add backup and no-replay fail-safe

            SELECT CONCAT(
                    'UPDATE prices SET price = ',
                    price * 2,
                    ', status = "active" WHERE id = ', id,
                    ' AND status = "inactive" AND price = ', price,
                    ';'
                )
                FROM prices WHERE status = 'inactive';

Our data

id	price	status
1	10.00	active
2	12.00	inactive
3	24.00	inactive
4	43.00	active
5	51.00	inactive

Result

            UPDATE prices SET price = 24.00, status = "active"
    WHERE id = 2 AND status = "inactive" AND price = 12.00;
            UPDATE prices SET price = 48.00, status = "active"
    WHERE id = 3 AND status = "inactive" AND price = 24.00;
            UPDATE prices SET price = 102.00, status = "active"
    WHERE id = 5 AND status = "inactive" AND price = 51.00;

What then?

Export to TSV (so that it would be one column, many lines)
Save as *.sql
Open and run
Re-run "Inception-SQL" - expect empty result
Attach both "Inception-SQL" and generated SQL file for backup info

SQL Inception

SELECT CONCAT('SQL', ' ', 'Inception');

Courtesy of Sergej Veliseičik

Hello, I'm Marius Balčytis

Problem

Example

Solution

Problem

Solution

Problem

Solution

How?

Select needed rows, review

Select SQL statements

Add backup and no-replay fail-safe

Our data

Result

What then?

Thanks!

Questions?