Update like insert

Question

Is it possible to perform update like insert?

UPDATE `table` SET `value` ('N','N','N','N','Y','Y','Y','N', 'N') WHERE `my_id` = '1'

The problem is that the number of values ​​to be inserted i dont now. It can be a 5 or 10.

Answer 1

replace is just like insert, it just checks if there is duplicate key and if it is it deletes the row, and inserts the new one, otherwise it just inserts

you can do this if there is for example unique index of (Name,Type) and if you type the following command

REPLACE INTO table1 (Name,Type,InitialValue,FinalValue) VALUES ('A',3,50,90 )

and there already exists a row with Name = 'A' and Type = 3 it will be replaced

CREATE UNIQUE INDEX idx_name_type ON table1(Name,Type)

EDIT: a quick note - REPLACE always DELETES and then INSERTs, so it is never a very good idea to use it in heavy load because it needs exclusive lock when it deletes, and then when it inserts

some of the database engines have

INSERT ... ON DUPLICATE KEY UPDATE ...

Answer 2

You have to specify the column-name.

UPDATE `table` SET `Col1`='y',`Col2`='n' ... WHERE `ID`='1'

Or want to update one or more columns/rows:

UPDATE `table` Set Col1='Y' WHERE `ID` IN ('1','11','13')