How do replace in all fields except one?

Question

I have a table with several fields, and I need to make a insert or replace on each line. I receive the data coming from a software and if it does not exist, I enter, otherwise I replace.

I get a array that may have all fields or only a few. Only that there are 2 fields in the table that I am adding.

campo_recebido1
campo_recebido2
campo_recebido3
...
campo_add1
campo_add2

For example, when the campo_recebido1 is equal to 1 I put a value on campo_add1 and when campo_recebido1 is equal to 2 I put a value on campo_add2.

To do this I use the REPLACE INTO automatically adjusted with the received fields.

Only I wanted to keep the campo_add1 and the campo_add2 with the last possible values. For this I made a Trigger:

CREATE TRIGGER upd_check BEFORE UPDATE ON table
    FOR EACH ROW
       BEGIN
          IF NEW.`campo_add1` IS NULL THEN
              SET NEW.`campo_add1` = OLD.`campo_add1`;
          END IF;
          IF NEW.`campo_add2` IS NULL THEN
              SET NEW.`campo_add2` = OLD.`campo_add2`;
          END IF;
       END;

I tested with UPDATE and INSERT but seems to do nothing.

How can I always keep campo_add which are not to be updated but are also not to be deleted?

---

EXAMPLES

EXAMPLE 1
I get 1,1234,"texto" and run the query:

REPLACE INTO table SET camporecebido1 = 1, camporecebido2 = 1234, camporecebido3 = "texto", campo_add1 = 32

How can you repair the campo_add2 is not part of the query and will be null.

EXAMPLE 2
Next I get 2,1234,"textoalterado" and run the query:

REPLACE INTO table SET camporecebido1 = 2, camporecebido2 = 1234, camporecebido3 = "textoalterado", campo_add2 = 64

How can you repair the campo_add1 is not part of the query, and will be deleted, but I wanted to keep the campo_add1 = 32 of the previous query...

EXAMPLE 3
If in the meantime I get 1,1234 run the query:

REPLACE INTO table SET camporecebido1 =1, camporecebido2 = 1234, campo_add1 = 128

That is, the camporecebido3 will be put to null, the campo_add1 updated and wanted to keep the meucampo2 = 64 of the previous query.

Set in the Sqlfiddle
Set in DB-Fiddle

---

Actually, as I understand it, what I need is an equivalent to INSTEAD OF trigger SQL Server but for Mysql.

Answer 1

Solution 1

One option I would use would be INSERT with ON DUPLICATE KEY UPDATE:

INSERT INTO table1 SET id="123", campo_recebido1 = "1", campo_recebido2 = "1234", campo_recebido3 = "texto", campo_add1 = "32"
ON DUPLICATE KEY UPDATE campo_recebido1 = "1", campo_recebido2 = "1234", campo_recebido3 = "texto", campo_add1 = "32";

INSERT INTO table1 SET id="123", campo_recebido1 = "2", campo_recebido2 = "1234", campo_recebido3 = "textoalterado", campo_add2 = "64"
ON DUPLICATE KEY UPDATE campo_recebido1 = "2", campo_recebido2 = "1234", campo_recebido3 = "textoalterado", campo_add2 = "64";

INSERT INTO table1 SET id="123", campo_recebido1 = "1", campo_recebido2 = "1234", campo_add1 = "128"
ON DUPLICATE KEY UPDATE campo_recebido1 = "1", campo_recebido2 = "1234", campo_add1 = "128";

---

Sqlfiddle

---

Difference between REPLACE and ON DUPLICATE KEY UPDATE

REPLACE

1. Try inserting the row in the table
2. If it fails, delete line and insert new line

ON DUPLICATE KEY UPDATE

1. Try inserting the row in the table
2. If it fails, refresh the line

Source: Why when using ON DUPLICATE KEY UPDATE or REPLACE, we have change in 2 lines?

---

Solution 2

CREATE TRIGGER upd_check BEFORE INSERT ON table1 FOR EACH ROW
BEGIN
  IF NEW.`campo_add1` IS NULL THEN 
    SET NEW.`campo_add1` = (SELECT `campo_add1` FROM table1 WHERE `id` = NEW.`id`);
  END IF;
  IF NEW.`campo_add2` IS NULL THEN 
    SET NEW.`campo_add2` = (SELECT `campo_add2` FROM table1 WHERE `id` = NEW.`id`);
  END IF;
END;//

REPLACE INTO table1 SET id="123", campo_recebido1 = "1", campo_recebido2 = "1234", campo_recebido3 = "texto", campo_add1 = "32"//
REPLACE INTO table1 SET id="123", campo_recebido1 = "2", campo_recebido2 = "1234", campo_recebido3 = "textoalterado", campo_add2 = "64"//
REPLACE INTO table1 SET id="123", campo_recebido1 = "1", campo_recebido2 = "1234", campo_add1 = "128"//

---

Dbfiddle

---

Like the REPLACE there are 2 options as seen above (or it gives INSERT or DELETE + INSERT) Trigger would have to be the INSERT to meet the 2 options, but being of the type INSERT using BEFORE or AFTER, you will not have the temporary table DELETED, having to manually fetch the value.

Source: In Trigger we have INSERTED, DELETED, but the "UPDATED"?

Answer 2

In this case use UPDATE instead of REPLACE

UPDATE table SET camporecebido1 = 1, camporecebido2 = 1234, camporecebido3 = "texto", meucampo1 = 32 WHERE XXXX=YYY

and

UPDATE table SET camporecebido1 = 2, camporecebido2 = 1234, camporecebido3 = "textoalterado", meucampo2 = 64  WHERE XXXX=YYY

Remembering to set the WHERE clause to update only the desired records.