combine INSERT/DELETE/UPDATE queries into one

Question

I have a main table that I use in my application called t_media. The dataset is from an external source that is updated every day. I download the dataset to a temporary table called t_media_temp every day. I need to do three things:

Insert a record from t_media_temp into t_media if it doesn't already exist
Update a record in t_media if the date is different in t_media_temp
Delete a record from t_media if it does not exist in t_media_temp

In the current situation I use the 3 queries beneath, but is there an easy way to combine it into a single query?

/* Insert */
INSERT INTO t_media (`col_1`, `col_2`, `col_3`, `col_x`) 
SELECT (`col_1`, `col_2`, `col_3`, `col_x`) 
FROM t_media_temp AS t1 
WHERE NOT EXISTS ( 
    SELECT * FROM t_media AS t2 
    WHERE t1.col_1 = t2.col_1 
)

/* Update */
UPDATE t_media, t_media_temp SET 
    t_media.col_1 = t_media_temp.col_1, 
    t_media.col_2 = t_media_temp.col_2, 
    t_media.col_3 = t_media_temp.col_3, 
    t_media.col_x = t_media_temp.col_x 
WHERE
    t_media.col_1 = t_media_temp.col_1 
AND 
    t_media.col_2 != t_media_temp.col_2

/* Delete */
DELETE FROM t_media WHERE col_1 NOT IN ( SELECT col_1 FROM t_media_temp )

col_1 is always a unique value in the table.

why logic around t_media.col_2 != t_media_temp.col_2 absent in insert query? — M0rtiis
– M0rtiis, Commented Jul 25, 2015 at 20:19

kolrie · Accepted Answer · 2015-07-25 20:19:34Z

2

According to documentation https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

You can mix your INSERT and UPDATE like this:

INSERT INTO t_media (`col_1`, `col_2`, `col_3`, `col_x`) 
SELECT (`col_1`, `col_2`, `col_3`, `col_x`) 
FROM t_media_temp AS t1
ON DUPLICATE KEY UPDATE
    t_media.col_2 = t1.col_2, 
    t_media.col_3 = t2.col_3, 
    t_media.col_x = t3.col_x

But there is no way to mix INSERT or UPDATE with DELETE

edited Jul 25, 2015 at 20:19

kolrie

12.8k15 gold badges70 silver badges102 bronze badges

answered Jul 25, 2015 at 20:17

M0rtiis

3,7611 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

web_student Over a year ago

Is col_1 automatically recognised as the duplicate key? And there is another condition... It has to be updated only is col_2 doesn't match.

M0rtiis Over a year ago

@web_student yes, col_1 will be automatically recognised as dupe if its a unique key (or part of it). abut col_2 i ve asked before. why we not see workaround it in your INSERT query? is it any key in table structure? can you post original fields names, not col_X?

web_student Over a year ago

what do you mean workaround in INSERT query? There is only one unique identifier and that is col_1. All the other values may not be unique. The table consists of 50+ columns.

Collectives™ on Stack Overflow

combine INSERT/DELETE/UPDATE queries into one

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related