如果item表的name字段为''就用resource_library 表的resource_name字段前面加上字符串review更新它,他们的关联关系在表resource_review_link中。
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.