mysql多表join时候update更新数据的方法

  • A+
所属分类：数据库

如果item表的name字段为''就用resource_library 表的resource_name字段前面加上字符串Review更新它,他们的关联关系在表resource_review_link中。

sql语句：

  1. 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
  2. and l.level='item' and r.resource_id=l.resource_id and i.name=''

JOIN UPDATE & JOIN DELETE

  1. update a
  2. set a.schoolname = b.schoolname
  3. from tb_Std as a join tb_Sch as b on a.School = b.School
  4. where a.std_year = 2005
  5. go
  6. /* 
  7. (2 row(s) affected) 
  8. */
  9. select *
  10. from tb_Std as a join tb_Sch as b on a.School = b.School
  11. /* 
  12. A School A A School 
  13. 2 2005 A A School A A School 
  14. 3 2004 C A School C C School 
  15. 4 2005 D D School D D School 
  16. (4 row(s) affected) 
  17. */
  1. delete a
  2. from table1 a, table2 b
  3. where a.col1 = b.col1
  4. 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.

图片引用自网络
1 2 3