Update like insert

问题:

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.

回答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 ...

回答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')