3

How can I achieve to Delete a row before Insert a new one in the same Table. I tried it with a Trigger but I read that it is not possible because it could cause a deadlock.

I also wanted to save the row which should be deleted to another table (example Table B) before delete it and then Insert a new one (into Table A).

Is there any other ways to do it ?

PS: They will have the same key

Ahmet K
  • 713
  • 18
  • 42

1 Answers1

0

You could use UPDATE... 

UPDATE tbl
SET col1 = newCol1,
    col2 = newCol2
WHERE etc = etc

And If you want to insert updated row to another table you could use TRIGGER AFTER UPDATE for that.

CREATE TRIGGER TriggerName ON Tbl
AFTER UPDATE
AS
INSERT INTO Log (Col1, Col2) 
SELECT Col1, Col2
FROM deleted
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • What if the Row not exists ? With this solution I only can do it when its already in my Table – Ahmet K Apr 29 '16 at 11:41
  • @AhmetKazaman In this case you could use `if exists (select ....) begin /*query to update*/ end else begin /*query to insert*/ end` – Stanislovas Kalašnikovas Apr 29 '16 at 11:43
  • You mean like this ? "DELIMITER // IF EXISTS (SELECT ... WHERE ID=...) BEGIN UPDATE ...; END// ELSE BEGIN INSERT ... END // DELIMITER ;" Because thats not working for me :"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN" . What Im doing wrong ? And will this effect the peformance of my query ? – Ahmet K Apr 29 '16 at 17:57
  • Does it even work in mysql ? – Ahmet K Apr 29 '16 at 18:00