mysql多表join时候update更新数据的方法
时间:2024-01-15 12:56:37
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.
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
javascript事件冒泡,事件捕获和事件委托详解
如何将txt文本中的数据轻松导入MySQL表中
JavaScript模板解析演示实例
Python基于内置库pytesseract实现图片验证码识别功能
MySQL慢sql优化思路详细讲解
![](https://img.aspxhome.com/file/2023/5/66145_0s.png)
mysql双向加密解密方式用法详解
为FCKeditor2.6添加行距功能(最新修改)
嵌入Flash应该考虑不支持Flash的浏览器
![](https://img.aspxhome.com/file/UploadPic/200712/20/20071220124122527s.jpg)
Django小白教程之Django用户注册与登录
![](https://img.aspxhome.com/file/2023/1/95921_0s.png)
Golang Gin 中间件 Next()方法示例详解
![](https://img.aspxhome.com/file/2023/9/103059_0s.png)
Mysql命令大全(完整版)
python 安装virtualenv和virtualenvwrapper的方法
python数据类型强制转换实例详解
商品评论的设计
![](https://img.aspxhome.com/file/UploadPic/20101/11/1-70s.jpg)
仿豆瓣分页原型(Javascript版)
Dreamweaver MX弹出窗口全攻略
![](https://img.aspxhome.com/file/UploadPic/20072/20072311313962s.jpg)
numpy中hstack vstack stack concatenate函数示例详解
Python爬虫之UserAgent的使用实例
理解Sql Server中的聚集索引
![](https://img.aspxhome.com/file/2023/3/91193_0s.png)
Python3 常用数据标准化方法详解
![](https://img.aspxhome.com/file/2023/8/118768_0s.jpg)