REPLACE INTO to update only 1 column?

Databases
1

I’m trying to do some kind of REPLACE INTO where only 1 field is updated where a key exists.

I’m reading a bunch of data from a file so I’d liketo do it all in a single query, rather than a series of INSERT INTO…ON DUPLICATE KEY UPDATE statements.

So I have this data:
unique_id, date, status
unique_id, date, status
…

And similar table structure.

I’d like to insert into the table but, if unique_id is already there, to update status but leave date unaffected. This rules out REPLACE INTO, doesn’t it? And INSERT INTO…ON DUPLICATE KEY UPDATE I couldn’t run on many lines in a single statement, could I?

Assuming the above won’t work, I seem to be left with doing 2 queries: 1 for inserting and 1 for updating, which I could maybe create a temporary or a derived table to do this.

Amy ideas?

2

From the manual

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT … ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT … UPDATE statements and returns NULL otherwise. Example:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

That statement is identical to the following two statements:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;

I never used it myself, but it seems that you can do multiple rows with 1 statement.

3

Wow. Thanks mate. Should have read the manual a bit harder, but that works like a charm! Cheers :slight_smile: