如果item表的name字段为''就用resource_library 表的resource_name字段前面加上字符串Review更新它,他们的关联关系在表resource_review_link中。
sql语句:
updateitemi,resource_libraryr,resource_review_linklseti.name=CONCAT('Review:',r.resource_name)wherei.item_id=l.instance_id
andl.level='item'andr.resource_id=l.resource_idandi.name=''
JOIN UPDATE & JOIN DELETE
updatea
seta.schoolname=b.schoolname
fromtb_Stdasajointb_Schasbona.School=b.School
wherea.std_year=
go
/*
(2row(s)affected)
*/
select*
fromtb_Stdasajointb_Schasbona.School=b.School
/*
ASchoolAASchool
2AASchoolAASchool
3CASchoolCCSchool
4DDSchoolDDSchool
(4row(s)affected)
*/
deletea
fromtable1a,table2b
wherea.col1=b.col1
anda.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.