Insert or update join?

[rfigley]

When inserting or updating, is there a join for these functions or are joins just used for extracting data, and multiple insert or update code used for putting selected data into their respective tables?

[Drakla]

Yup, you can use join syntax in other queries, here's a few useful examples - this first one gets rid of entries in the affiljoin table whose user has now been deleted

DELETE FROM affiljoin
USING affiljoin LEFT JOIN users
ON
affiljoin.id = users.amaffil
WHERE users.id IS NULL;

// NOTE you can use any field in the unmatched row as all of them will be null anyway
// OR USE SYNTAX

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

Update can use it, too - this for instance

Orphaned urls in my urls database can be set to 'ungrouped' which has id zero like so

$q = "UPDATE urls
LEFT JOIN groups
ON urls.groupid = groups.groupid
SET urls.groupid = 0
WHERE groups.groupid IS NULL
";

// other update syntax

UPDATE score, grade_event SET score.score = score.score + 1
WHERE score.event_id = grade_event.event_id
AND grade_event.date = '2004-09-23' AND grade_event.category = 'Q';

UPDATE t1, t2 SET t2.a = t1.a WHERE t2.id = t1.id;

And with insert take a look for "INSERT SELECT SYNTAX" on google, as that really is brilliant, especially when creating temporary tables if your database doesn't allow subqueries.