mysql多表join时候update更新数据的方法
程序员文章站
2024-02-11 13:06:58
sql语句:复制代码 代码如下:update item i,resource_library r,resource_review_link l set i.name=con...
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.
复制代码 代码如下:
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.
上一篇: Html5剪切板功能的实现代码
下一篇: Java字符串去除特殊字符内容的实例
推荐阅读
-
mysql多表join时候update更新数据的方法
-
Mysql更新字段中部分数据的方法_MySQL
-
SQL Update多表联合更新的方法 博客分类: sqlserver@数据库 sql
-
SqlServer 数据更新的时候怎么把sql语句传到MySQL
-
mysql多表join时候update更新数据的方法
-
Mysql更新字段中部分数据的方法_MySQL
-
mysql如果数据不存在,则插入新数据,否则更新的实现方法_MySQL
-
php mysql php mysql 判断update之后是否更新了的方法
-
mysql如果数据不存在,则插入新数据,否则更新的实现方法_MySQL
-
Mysql 数据库更新错误的解决方法