Uncommon Database Queries

Update from Select

MySQL - Update only a single column

UPDATE b T 
  SET v1 = (SELECT v1 FROM a S WHERE T.id = S.id);

MySQL - Update multiple columns in a single query

UPDATE b T
  LEFT OUTER JOIN a S
 ON ((T.id = S.id) AND (S.v1 IN ('a', 'b', 'c')))
SET
  T.v1 = S.v1,
  T.v2 = IF(S.v2 > 100, 'High', 'Low');

INNER JOIN will also work.

Delete with Joins

MySQL - From Single Table

DELETE t2
  FROM t2 INNER JOIN t1 ON (t2.ref = t1.id)
WHERE t1.type = 100;

Ref:

• MySQL Tutorial