mysql join update

有時候刪除資料必須關聯另一張表才能篩選出來

就必須update和join 同時

update table1 ,table2

set table1.column = table2.column

where table1.column2 = table2.column2

或者

update table1 inner join table2 on (table1.column2 = table2.column2)

set  table1.column = table2.column

where table1.column2 = table2.column2

那delete呢?

其實也是一樣

delete
from table1   

    using  table1    join table2  on table1.column2 = table2.column2
where  ....

MSSQL 作法如下，順便加上子查詢範例


create table Table_A (id int,col_a int )

create table Table_B (mid int , id int,col_b int )



update Table_A
set col_count = Table_B.col_a
from  (select mid,count(col_a) as col_a  from Table_B  where col_b = 1 group by mid) Table_B
where Table_A.id = Table_B.mid