sqlupdatedelete中使用innerjoin

2023年8月3日发(作者：)

sqlupdatedelete中使⽤innerjoinUpdate

Update XXX set XXX where 这种写法⼤家肯定都知道，才发现update和delete居然⽀持inner join的update⽅式，这个在表间关联来做更新和删除操作⾮常有⽤.列⼦: update tb_User

set pass=''

from tb_User usr

inner join tb_Address addr on ssFK = ssID

where =123

update的格式是update t1 set =’Liu’ from t1 inner join t2 on = QL，ACCESS 写法如下:

UPDATE mem_world AS mw1 INNER JOIN mem_world AS mw2

ON id =

SET =

WHERE = 107

AND id = 0

AND > 1;

on是表连接的筛选条件

就是说，表连接后，会产⽣⼀个类似于临时的视图这么⼀个东西

where是从这个临时的视图中筛选数据的

所以，你⾸先要搞清，你的所谓的2个条件属于哪⼀种Deletedelete 语句也是类似delete from t1

from t1 inner join t2 on = 注意蓝⾊部分。mysql:

DELETE mwb FROM mem_world_building AS mwb INNER JOIN mem_world AS mw

ON =

where between 11 and 15

and baseid = 107

and id <> 0

and > 1;

下⾯是ORACLE的: DELETE TABLE1 where exists ( select 1 from table2 where and = and FWDWID=8);

DELETE TABLE1 where KHID exists ( select KHID from table2 where FWDWID=8)

2023年8月3日发(作者：)

sqlupdatedelete中使⽤innerjoinUpdate

Update XXX set XXX where 这种写法⼤家肯定都知道，才发现update和delete居然⽀持inner join的update⽅式，这个在表间关联来做更新和删除操作⾮常有⽤.列⼦: update tb_User

set pass=''

from tb_User usr

inner join tb_Address addr on ssFK = ssID

where =123

update的格式是update t1 set =’Liu’ from t1 inner join t2 on = QL，ACCESS 写法如下:

UPDATE mem_world AS mw1 INNER JOIN mem_world AS mw2

ON id =

SET =

WHERE = 107

AND id = 0

AND > 1;

on是表连接的筛选条件

就是说，表连接后，会产⽣⼀个类似于临时的视图这么⼀个东西

where是从这个临时的视图中筛选数据的

所以，你⾸先要搞清，你的所谓的2个条件属于哪⼀种Deletedelete 语句也是类似delete from t1

from t1 inner join t2 on = 注意蓝⾊部分。mysql:

DELETE mwb FROM mem_world_building AS mwb INNER JOIN mem_world AS mw

ON =

where between 11 and 15

and baseid = 107

and id <> 0

and > 1;

下⾯是ORACLE的: DELETE TABLE1 where exists ( select 1 from table2 where and = and FWDWID=8);

DELETE TABLE1 where KHID exists ( select KHID from table2 where FWDWID=8)