CODEWITHSUNDEEP
mysqlmultiple-tables
Valentino
Valentino

Reputation: 542

join update of multiple rows on child table

parent_table:
ID  | FIELD1 | FIELD2
____|________|________
1   |........|......
2   |........|.....
3   |.............. 

child_table:
ID | FIELD3
___|________
1  |   18
1  |   7
2  |   ..
1  |   ..
3  |   ..
2  |   ..

Let's say I want to update these tables. This is what I need to achieve:

 UPDATE parent_table SET 
   FIELD1 = 'XXX', 
   FIELD2 = 'YYY' 
 WHERE ID = 2;

 DELETE FROM child_table 
 WHERE ID = 2;

 INSERT INTO child_table (ID, FIELD3)
   VALUES (2, 50),
   VALUES (2, 17),
   VALUES (2, 9);

note that the number of entries in the child table for each ID may vary during the update.

Is it possible to get the same result in just one query? I can't use a transaction.

maybe the first two queries are easier to merge. I tried to join the child table and set null values during the UPDATE, but I don't know how to handle the multiple rows.. Is it possible to have nested UPDATE like you have nested SELECT?

Upvotes: 1

Views: 738

Answers (1)

Nir Alfasi
Nir Alfasi

Reputation: 53535

You can update multiple tables in one command:

UPDATE parent_table p LEFT JOIN child_table c On p.id = c.id
 SET 
   p.FIELD1 = 'XXX', 
   p.FIELD2 = 'YYY',
   c.FIELD3 = ''
 WHERE p.ID = 2;

However, I don't think that you can preform update and delete in the same command - but I must admit I'm not sure about it.

You can read more about multiple tables update here

A different (and preferred) approach will be to create a trigger on parent_table that will delete the relevant records from child_table upon update.

Upvotes: 1

Related Questions