当前位置： 移动技术网 > IT编程>数据库>Mysql > mysql多表join时候update更新数据的方法

mysql多表join时候update更新数据的方法

2017年12月12日  | 移动技术网IT编程  | 我要评论
sql语句：
复制代码 代码如下:

update item i,resource_library r,resource_review_link l set i.name=concat('review:',r.resource_name) where i.item_id=l.instance_id
and l.level='item' and r.resource_id=l.resource_id and i.name=''


join update & join delete
复制代码 代码如下:

update a
set a.schoolname = b.schoolname
from tb_std as a join tb_sch as b on a.school = b.school
where a.std_year = 2005
go
/*
(2 row(s) affected)
*/
select *
from tb_std as a join tb_sch as b on a.school = b.school
/*
a school a a school
2 2005 a a school a a school
3 2004 c a school c c school
4 2005 d d school d d school
(4 row(s) affected)
*/

复制代码 代码如下:

delete a
from table1 a, table2 b
where a.col1 = b.col1
and a.col2 = b.col2

the above sql statement runs fine in sql server.
if the oracle 9i has different syntax or if there is any other way to accomplish this with a single delete statement that would be really helpful.

> hi,
>
> is the following delete statement possible in oracle 9i.
>
> delete a
> from table1 a, table2 b
> where a.col1 = b.col1
> and a.col2 = b.col2
>
> the above sql statement runs fine in sql server.
>
> if the oracle 9i has different syntax or if there is any other way to accomplish this with a single delete statement that would be really helpful.
>
> thanx in advance.
>
> -bheem
bheem,
try this:
delete from table1 a where exists (select 1 from table2 b
where a.col1 = b.col1 and a.col2 = b.col2);
hope this helps,
tom k.

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网