I have a table with several fields, and I need to make a insert or replace on each line. I get the data coming from a software and if it does not exist, I insert, if not replaced.
I get a array that can have all or just a few fields. But there are 2 fields in the table that I'm adding.
camporecebido1
camporecebido2
camporecebido3
...
meucampo1
meucampo2
For example, when camporecebido1 is equal to 1 I put a value in meucampo1 and when camporecebido1 is equal to 2 I put a value in meucampo2 .
In order to do this I use REPLACE INTO automatically adjusted with the received fields.
I just wanted to keep meucampo1 and meucampo2 with the last possible values. For this I did a trigger:
CREATE TRIGGER upd_check BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
IF NEW.'campo1' IS NULL THEN
SET NEW.'campo1' = OLD.'campo1';
END IF;
IF NEW.'campo2' IS NULL THEN
SET NEW.'campo2' = OLD.'campo2';
END IF;
END;
I tested with UPDATE and with INSERT but it seems to do nothing.
Any idea how I can always keep my field that is not to be updated but also not to be deleted?
I'll put an example to better understand:
EXAMPLE 1
I get 1,1234,"texto" and execute the query:
REPLACE INTO table SET camporecebido1 = 1, camporecebido2 = 1234, camporecebido3 = "texto", meucampo1 = 32
How can they repair meucampo2 is not part of the query and will be null.
EXAMPLE 2
Then I get 2,1234,"textoalterado" and execute the query:
REPLACE INTO table SET camporecebido1 = 2, camporecebido2 = 1234, camporecebido3 = "textoalterado", meucampo2 = 64
How can you fix meucampo1 is not part of the query, and will be deleted, but I wanted to keep meucampo1 = 32 of the previous query ...
EXAMPLE 3
If however I receive 1,1234 execute the query:
REPLACE INTO table SET camporecebido1 =1, camporecebido2 = 1234, meucampo1 = 128
That is, camporecebido3 will be set to null, meucampo1 updated and wanted to keep meucampo2 = 64 of the previous query.