Update a table only if updated field is higher

Question

I'm triying to upload some files with incremental data. The tabla has a PK (_id), but this _id could appears in many files with different values.

I'd like to load massively the files with a REPLACE INTO or similar, if the register doesn't exist then it must be inserted, but if the register already exists, it must check if the field updated is later than the other one or not, and if it is, replace it, and if it is not, ignore. Is this possible?

I have tried to do several tests with REPLACE and UPDATE or create a temporary table but without results

Barmar · Accepted Answer · 2024-05-29 21:13:23Z

0

Use conditional code in the ON DUPLICATE KEY UPDATE clause.

INSERT INTO yourTable
SELECT * 
FROM tempTable
ORDER BY updated
ON DUPLICATE KEY UPDATE 
    col1 = IF(NEW.updated > updated, NEW.col1, col1),
    col2 = IF(NEW.updated > updated, NEW.col2, col2),
    -- repeat for all columns

If you're using pre-8.x MySQL, use VALUES(columnname) instead of NEW.columnname

edited May 29, 2024 at 21:13

answered May 29, 2024 at 21:00

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

8 Comments

JJBB Over a year ago

Thanks. I assume that the conditional table should not have a PK in the sense that it should allow duplicate values, so that they can then be evaluated.

Barmar Over a year ago

Do you mean tempTable._id? I assumed it would also be a PK, and it's unique in each file. You would load the file into the temp table, run this query, then empty the table and repeat with other files.

Barmar Over a year ago

If it can be duplicated in tempTable, and there are multiple rows that satisfy NEW.updated > updated, it's unpredictable which one will be used.

Barmar Over a year ago

Although you could use ORDER BY updated so it will always use the most recent one.

JJBB Over a year ago

I had planned to load multiple files into the temp table at the same time; even if they had the same _id, and then run the query. Would that work?

|

Collectives™ on Stack Overflow

Update a table only if updated field is higher

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related