mysql 5.5 中自連線update and delete

markzy5201190發表於2012-07-11
MySqldelete
如:
Id   Role_Id   Source_Id  Soul_Num
1 2 322009 10
2 2 321009 7
3 1 322009 2
4 1 321009 0
5 3 322009 5
6 4 322009 3
獲得:
Id   Role_Id   Source_Id  Soul_Num
2 3 321009 27
4 1 321009 4
5 3 321009 5
6 4 321009 3
SQL如下:

start transaction;
update altar_soul_x
inner join (select Role_Id,Source_Id,Soul_Num SoulNum from altar_soul_x where Source_Id=322009) b on a.Role_Id=b.Role_Id and a.Source_Id=321009
SET a.Soul_Num = a.Soul_Num + b.SoulNum;


delete a
from altar_soul_x a
inner join (select Role_Id,Source_Id SourceId,Soul_Num SoulNum from altar_soul_x where Source_Id=321009) b on a.Role_Id=b.Role_Id;

update altar_soul_x set Source_Id = 321009 where Source_Id = 322009;

rollback;

不適合:Version:mysql5.1 ，如下語法適合該版本:

主要注意delete / update 語法:
SQL功能：保留tbl中Level最大的值,其餘剔除:
DELETE test.tbl e FROM test.tbl e
INNER JOIN (SELECT Role_Id,Pac_Level FROM(SELECT Role_Id,MAX(Pac_Level)Pac_Level FROM
test.tbl where Pac_Type=7 GROUP BY Role_Id HAVING COUNT(0)>1)a)f ON e.Role_Id = f.Role_Id
WHERE e.Pac_Type=7 AND e.Pac_Level
UPDATE DBName.thanks_tbl a
LEFT JOIN dbname.giving_tbl b ON a.RId = b.RId AND a.C_Type = b.C_Type
SET a.State = 0
where a.C_Type <> 1 and a.State =1 AND b.C_Type IS NULL;







來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26855487/viewspace-735201/，如需轉載，請註明出處，否則將追究法律責任。

相關文章