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

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.

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

如您喜欢交流学习经验，点击链接加入交流1群：1065694478(已满)交流2群：163560250