I added some entries from table2 to table1 as

INSERT INTO table1 (title) SELECT title from table2 WHERE ...

how can I UPDATE table2 for the SELECTed or INSERTed entries as

UPDATE table2 SET status='Used'
  • 1) INSERT .. VALUES SELECT .. not exists. Either INSERT .. VALUES .. or INSERT .. SELECT ... 2) One query cannot have both INSERT and UPDATE type - so you need at least 2 queries. But I recommend 3-step way - mark for insert, insert, update marked. 3) Does table1.title have unique constraint? – Akina Oct 17 '18 at 13:31
  • Your question is not clear, please try to improve it by adding some sample data and the desired result. – McNets Oct 18 '18 at 7:06

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;
| improve this answer | |
  • I think the condition looks like WHERE foo = bar AND status != 'Used' ... – Akina Oct 17 '18 at 17:08

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 

The statement INSERTs rows on table1 unless the new row would cause a duplicate primary key, in that case it does an UPDATE on the status column.

| improve this answer | |
  • Sorry, this is wrong. The update in this statement works on the table which is inserted into, in this case table1. You want to update table2, though. – tombom Oct 17 '18 at 14:00
  • My bad, you are right. Your answer is the correct one. For completeness sake I would only check that the isolation level is repeatable read (which is the default). Maybe it would be a good idea to add set session transaction isolation level REPEATABLE READ; before starting the transaction. – socaire Oct 17 '18 at 14:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.