Delete not updated rows after upsert

user_1067688 0
Viewed 1151

I have a table tha contains 3 rows like:

P_K, Group, Name, Age:
1,   1,     Alice, 35
2,   1,     Bob,   40
3,   2,     Eva,   19
4,   2,     Carl,  20

And I want (if possible) with 1 MySQL query to update Alice's age and delete Bob.

My first approach was to execute a delete query on group 1, and then insert the rows that I want. (1, Alice, 36)

Later on I found an other approach, which was to add an extra column with a timestamp or a number, and during the upsert update this column. Then delete the rows that not mach the latest number.

Is there something else that I can do? That doesn't demand an extra column or 2 queries?

mysql sql
1 Answers

May be you can do it by creating a procedure...

CREATE PROCEDURE example
BEGIN

UPDATE table SET table.something=1 WHERE table.something=x;

DELETE FROM table WHERE table.something=Y;

END;

EXECUTE example;

I dont know if it will work or not, just trying

Another Solution as you don't know the value of "Y" (As given above in the code)

Procedure with parameters

CREATE PROCEDURE example @Y <datatype> //varchar(20)
    BEGIN

    UPDATE table SET table.something=1 WHERE table.something=x;

    DELETE FROM table WHERE table.something=y;

    END;

    EXECUTE example @Y='value';
user_18845490
Related