Update from one mysql table to another

Something I did a lot of today. Updating from one table in mysql to another:


UPDATE table2 t2, table1 t1
SET t1.t2name = t2.name
WHERE
t1.id = t2.id

Updating a table in SQL where records in table A don't exist in table B.

I find myself doing this a lot recently, so I'm posting it here too. Hopefully it is SQL92 and should run on just about anything.


1. Updating a table in SQL where records in table A don't exist in table B:

insert into tableB (field1, field2, field3)
select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 like '%bleh%'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2);



2. Hey, and why not do something silly like have a union query and some limit in the mix (limit works in MySQL only):

insert into tableB (field1, field2, field3)
select field1, field2, field3 from
(select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 like 'bleh'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2)
limit 10) A1
union
select field1, field2, field3 from
(select f1 as field1, f2 as field2, f3 as field3
from tableA A
WHERE
A.f1 not like 'bleh'
and not exists(SELECT * FROM tableB WHERE f1=A.f1 and f2=A.f2)
limit 10) A2;