mysql多表join時候update更新數據的方法
更新時間：2011年03月31日 00:52:04 作者：
如果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.
復制代碼 代碼如下:
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.
相關文章
login_path是MySQL5.6開始支持的新特性,本文主要介紹了MySQL通過login_path登錄數據庫,文中通過示例代碼介紹的非常詳細,需要的朋友們下面隨著小編來一起學習學習吧2025-02-02
MySQL運行報錯:“Expression?#1?of?SELECT?list?is?not?in?GR
這篇文章主要給大家介紹了關于MySQL運行報錯:“Expression?#1?of?SELECT?list?is?not?in?GROUP?BY?clause?and?contains?nonaggre”的解決方法,文中將解決方法介紹的非常詳細,需要的朋友可以參考下2022-06-06
文章介紹了在數據庫操作中,如何使用`REPLACE INTO`和`INSERT INTO ON DUPLICATE KEY UPDATE`語句進行數據更新和插入操作,如果不想創(chuàng)建唯一性索引,可以通過存儲過程實現,文章通過實驗和驗證,展示了這兩種方法的實際效果2024-12-12
在使用select語句時可以結合order by對查詢的數據進行排序。如果不使用order by默認情況下MySQL返回的數據集,與它在底層表中的順序相同,可能與你添加數據到表中的順序一致,也可能不一致，如果希望得到的數據有順序,就該明確排序方式,下面詳細內容需要的朋友可以參考一下2021-11-11
這篇文章主要介紹了mysql存儲過程如何利用臨時表返回結果集，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-09-09
mysql一條sql查出多個條件不同的sum或count問題
這篇文章主要介紹了mysql一條sql查出多個條件不同的sum或count問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-05-05
這篇文章主要介紹了MySQL基本查詢示例總結,本文通過實例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2025-05-05
文件存儲是我們日常開發(fā)中經常遇到的一個功能，下面這篇文章主要給大家介紹了關于Mysql文件存儲的相關資料，需要的朋友可以參考下2021-06-06
MySQL存儲引擎應用場景MyISAM?vs?InnoDB優(yōu)勢選擇
這篇文章主要為大家介紹了MySQL存儲引擎應用場景MyISAM?vs?InnoDB優(yōu)勢選擇,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-10-10
在本文中，總結了開發(fā)過程中最為常見的幾種 MySQL 拋出的異常以及如何解決，包括高版本驅動的問題、時區(qū)配置問題、SSL 連接問題等。于我個人而言，這一篇足以解決目前項目中所有遇到的MySQL問題。同時，也希望本文能對 MySQL 數據庫初學者有一定的引導入門作用。2021-05-05