MySQL - Overwriting existing rows

Question

I'm using an ID system and I'm trying to overwrite my entire row if the ID is the same. I used what was suggested by eggyal in the following post: SQL - IF EXISTS UPDATE ELSE INSERT INTO

INSERT INTO Data_test (SN, Inspect_Date, Inspector_Name, Cable_Check) VALUES ('SN003400', CURRENT_TIMESTAMP, 'Jack', 1)
ON DUPLICATE KEY UPDATE
    SN = VALUES(SN), Inspect_Date = VALUES(Inspect_Date), Inspector_Name= VALUES(Inspector_Name), Cable_Check= VALUES(Cable_Check);

And this does exactly what I need but I did receive an error that the VALUES() function will be removed in a future release.

Warning: 'VALUES function' is deprecated and will be removed in a future release. Please use and alias (INSERT INTO ... VALUES(...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

I'm new to SQL and I'm not really getting what is meant with this alias. My question here is what I can use instead and what would be the best practice for this.

Show us your problem statement. – jarlh Apr 07 '21 at 09:50 — jarlh, Apr 07 '21 at 09:50
@jarlh edited my post with the query – Shanck Apr 07 '21 at 10:00 — Shanck, Apr 07 '21 at 10:00

score 1 · Accepted Answer · answered Apr 07 '21 at 10:10

You need to add an alias to the VALUES clause and then use that alias

INSERT INTO Data_test (SN, Inspect_Date, Inspector_Name, Cable_Check) VALUES ('SN003400', CURRENT_TIMESTAMP, 'Jack', 1)
ON DUPLICATE KEY UPDATE
    SN = VALUES(SN), Inspect_Date = VALUES(Inspect_Date), Inspector_Name= VALUES(Inspector_Name), Cable_Check= VALUES(Cable_Check);

To

INSERT INTO Data_test (SN, Inspect_Date, Inspector_Name, Cable_Check) VALUES ('SN003400', CURRENT_TIMESTAMP, 'Jack', 1) AS newalias
    ON DUPLICATE KEY UPDATE
        SN = newalias.sn, Inspect_Date = newalias.Inspect_Date, Inspector_Name= newalias.Inspector_Name, Cable_Check= newalias.Cable_Check;

MySQL - Overwriting existing rows

1 Answers1