15

i saw this

In MySQL, joins work for INSERT, UPDATE, and DELETE statements. It's possible to change data in more than one table when joining tables in an UPDATE or DELETE statement.

in an answer for a question from the mysql certification guide. is it true? inserts with joins? an example of it?

Improve this question

3 Answers 3

Reset to default
26

You can INSERT ... SELECT with mysql, which is probably what they mean. For example:

INSERT INTO tableNew (col1, col2)
  SELECT tbl1.col1, tbl2.col2
  FROM tbl1 JOIN tbl2
Improve this answer
2
  • 2
    Consider using REPLACE instead of INSERT, it updates duplicates OR inserts missing values
    – Daniel W.
    Commented Nov 11, 2013 at 11:46
  • 1
    @Marius, What about inserting into the joined table (the two tables) like we can with delete? stackoverflow.com/a/3860308/632951
    – Pacerier
    Commented May 10, 2015 at 7:52
12

To complete the set, here's one for DELETE. This is a common method for deleting rows together with their dependencies without triggers.

DELETE users, comments
FROM users JOIN comments ON comments.author=users.id
WHERE users.isspammer=1
Improve this answer
8

You can do this for Update statement like this,

Update C
Join tableB B on B.id=C.bid
Join tableA A on A.id=B.aid
Set C.status='Active',
A.status='Active'
Where A.id=1 or A.id=2 or A.id=3

check this for your reference.

http://www.siusic.com/wphchen/mysql-update-statement-159.html

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

Improve this answer

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