SQL statement:
Copy Code code as follows:
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
Copy Code code as follows:
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 a School a School
3 C A School c c School
4 D d School D D School
(4 row (s) affected)
*/
Copy Code code as follows:
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 are any other way to accomplish this with a single DELETE statement 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 are any other way to accomplish this with a single DELETE statement That would is 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.