0

I have simple mySql question similar to SO "Update mysql column" I have a table table1 with at least two columns columns and a bunch of rows: [key_col|col_a|col_other] I want to update one column col_a if key_col=number exist or insert if not exists. I first must do select or update do insert automatically?

Saku
  • 383
  • 2
  • 12

2 Answers2

2

See this answer: Insert into a MySQL table or update if exists

What you said: "I want to update if key exists or insert if not exists" is equivalent to the opposite order: "insert if not exists or update if exists", because the 2 cases are mutually exclusive.

First make sure that key_col has a unique index (like: primary key). Then this would work:

insert into
    `table1`
    (`key_col`, `col_a`, ...)
values
    (123, 234, ...)
on duplicate key update
    `col_a` = 234, ...;

At "..." place the other fields.

Community
  • 1
  • 1
Crouching Kitten
  • 1,135
  • 12
  • 23
0

if key_col is a primary key then do a replace into instead of doing insert into.....replace into will insert if the key does not exist else update if the key exists.

prashant
  • 1,382
  • 1
  • 13
  • 19
  • http://dev.mysql.com/doc/refman/5.7/en/replace.html "REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted" - how change only one field as UPDATE and preserve other field? – Saku Jan 03 '17 at 19:26
  • Is it possible to define a composite unique key on all other fields apart from the one being updated – prashant Jan 03 '17 at 19:33
  • SO "mysql REPLACE INTO only some fields" - INSERT INTO gfs (localidad, fecha, pp) VALUES ('some_place', '2012-08-05 02:00', 1.6) ON DUPLICATE KEY UPDATE pp=VALUES(pp); – Saku Jan 03 '17 at 19:42
  • yeah....correct.....the some fields would be a combination of primary + non primary and you would only be updating non primary in case primary exists – prashant Jan 03 '17 at 19:44