I added some entries from
INSERT INTO table1 (title) SELECT title from table2 WHERE ...
how can I UPDATE
table2 for the
INSERTed entries as
UPDATE table2 SET status='Used'
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Not sure, what you are after. There is no automated way to do this. You could of course write a trigger, but that would execute for every insert that happens on table1, which is most likely not what you want.
Usually you would just put your statements in a transaction and put a lock on the to be updated rows. Like this:
start transaction; insert into table1(title) select title from table2 where foo=bar for update; update table2 set status= 'Used' where foo=bar; commit;
You could try using
INSERT INTO ... ON DUPLICATE KEY UPDATE. This will only work if you have PRIMARY KEY (title) on table1.
INSERT INTO table1 (title) SELECT title FROM table2 ON DUPLICATE KEY UPDATE status = 'Used'
INSERTs rows on table1 unless the new row would cause a duplicate primary key, in that case it does an
UPDATE on the