1. 云栖社区>
  2. >
  3. 正文

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

作者:用户 来源:互联网 时间:2016-03-31 10:34:53

joinupdate

mysql多表join时候update更新数据的方法_Mysql - 摘要: 本文讲的是mysql多表join时候update更新数据的方法_Mysql, sql语句: 复制代码 代码如下: update item i,resource_library r,resource_review_link l set i.name=CONCAT('Review:',r.resource_name)

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.

以上是云栖社区小编为您精心准备的的内容,在云栖社区的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索join update mysql update join、mysql update join on、mysql update join 表、mysql join 更新、mysql update批量更新,以便于您获取更多的相关知识。

稳定可靠、可弹性伸缩的在线数据库服务,全球最受欢迎的开源数据库之一

6款热门基础云产品6个月免费体验;2款产品1年体验;1款产品2年体验

弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率

开发者常用软件,超百款实用软件一站式提供