-2

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'

closed as unclear what you're asking by mustaccio, Md Haidar Ali Khan, McNets, John aka hot2use, John Eisbrener Oct 22 '18 at 15:40

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
2

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

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'

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.

  • 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.