IF ELSE insert or replace in a query based on whether the table has a row

Asked Apr 18 '19 at 22:11

Active Apr 18 '19 at 22:15

Viewed 16 times

I'm trying to accomplish something like this? How could I be able to do something like this in a query?

The caveat is that I want to change the old pk so for example if it was 888 I want to change it to 999 and still only have 1 row in the table.

SET @toReplaceInto := (SELECT `pk` FROM `my_table` ORDER BY `pk` DESC LIMIT 1);
IF @toReplaceInto = NULL THEN
INSERT INTO `my_table` 
  (`pk`,`number`) VALUES (999,2);
ELSE
REPLACE INTO `my_table` 
  (`pk`,`number`) VALUES (999,2,2,3,4,5,6,7) WHERE `pk` = @toReplaceInto;
END IF;
``

edited Apr 18 '19 at 22:14

asked Apr 18 '19 at 22:11

wayfarer

Use `INSERT` with the `ON DUPLICATE KEY UPDATE` option. – Barmar Apr 18 '19 at 22:12
Could you please take a look at my updates to this question? @Barmar – wayfarer Apr 18 '19 at 22:15
You can't do this in a single query. You'll have to write a procedure. – Barmar Apr 18 '19 at 22:16
What if there's already `pk = 999` but it's not the highest key? – Barmar Apr 18 '19 at 22:18
I don't care in this case, I would prefer to do nothing – wayfarer Apr 18 '19 at 22:19

IF ELSE insert or replace in a query based on whether the table has a row

0 Answers0