MySQL Insert & Joins

Question

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?

score 26 · Accepted Answer · answered Sep 05 '09 at 09:13

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

answered Sep 05 '09 at 09:13

Marius

57,995
32
132
151

2

Consider using REPLACE instead of INSERT, it updates duplicates OR inserts missing values – Daniel W. Nov 11 '13 at 11:46
1

@Marius, What about inserting into the joined table (the two tables) like we can with `delete`? http://stackoverflow.com/a/3860308/632951 – Pacerier May 10 '15 at 07:52

score 12 · Answer 2 · answered Sep 05 '09 at 11:46

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

answered Sep 05 '09 at 11:46

bobince

528,062
107
651
834

score 8 · Answer 3 · answered Sep 05 '09 at 09:33

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

MySQL Insert & Joins

3 Answers3

Linked