Oracle\MS SQL Server Update多表關聯更新
一條Update
更新語句是不能更新多張表的,除非使用觸發器隱含更新。而表的更新操作中,在很多情況下需要在表示式中引用要更新的表以外的資料。我們先來討論根據其他表資料更新你要更新的表
一、MS SQL Server 多表關聯更新
sql server提供了update
的from
子句,可以將要更新的表與其它的資料來源連線起來。雖然只能對一個表進行更新,但是通過將要更新的表與其它的資料來源連線起來,就可以在update
的表示式 中引用要更新的表以外的其它資料。
一般形式:
update A SET 欄位1=B表欄位表示式, 欄位2=B表欄位表示式
from B WHERE 邏輯表示式
例如:
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
實際更新的操作是在要更新的表上進行的,而不是在from
子句所形成的新的結果集上進行的
二、Oracle 多表關聯更新
Oracle沒有update from
語法,可以通過兩種實現方式:
1、利用子查詢:
update A
SET 欄位1=(select 欄位表示式 from B WHERE ...),
欄位2=(select 欄位表示式 from B WHERE ...)
WHERE 邏輯表示式
UPDATE
多個欄位兩種寫法:
寫法一:
UPDATE table_1 a
SET col_x1 = (SELECT b.col_y1,
b.col_y2
FROM table_2 b
WHERE b.col_n = a.col_m),
col_x2 = (SELECT b.col_y2
FROM table_2 b
WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT *
FROM table_2 b
WHERE b.col_n = a.col_m)
或
UPDATE table_1 a
SET col_x1 = (SELECT b.col_y1,
b.col_y2
FROM table_2 b
WHERE b.col_n = a.col_m),
col_x2 = (SELECT b.col_y2
FROM table_2 b
WHERE b.col_n = a.col_m)
WHERE a.col_m = (SELECT b.col_n
FROM table_2 b
WHERE b.col_n = a.col_m)
寫法二:
UPDATE table_1 a
SET (col_x1, col_x2) = (SELECT b.col_y1,
b.col_y2
FROM table_2 b
WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT *
FROM table_2 b
WHERE b.col_n = a.col_m);
或
UPDATE table_1 a
SET ( col_x1, col_x2 ) = (SELECT b.col_y1,
b.col_y2
FROM table_2 b
WHERE b.col_n = a.col_m)
WHERE a.col_m = (SELECT b.col_n
FROM table_2 b
WHERE b.col_n = a.col_m)
注意:
- 對於子查詢的值只能是一個唯一值,不能是多值。
- 子查詢在絕大多數情況下,最後面的
where EXISTS
子句是重要的,否則將得到錯誤的結果。且where EXISTS
子句可用另一方法代替,如上。最後的子句是對a表被更新記錄的限制,如無此句,對於a表中某記錄,如在b表中關聯不到對應的記錄,則該記錄被更新欄位將被更新為null
。where EXISTS
子句就是排除對a表中該情況的記錄進行更新。
2、利用檢視:
UPDATE (SELECT a.name ANAME,
b.name BNAME
FROM a,
b
WHERE a.id = b.id)
SET aname = bname;
注意:
- 對於檢視更新的限制:
如果檢視基於多個表的連線,那麼使用者更新(update
)檢視記錄的能力將受到限制。除非update
只涉及一個表且檢視列中包含了被更新的表的整個主鍵,否則不能更新檢視的基表。
另外,Oracle中的Delete
的from
子句也沒有多表聯接的功能,只能通過子查詢的方式來做:
delete from 表A where exists (select * from 表B where 表A.empid=表B.empid)
delete from 表A where 表A.empid in (select empid from 表B)
三、oracle檢視多表更新
在oracle中通常如果檢視的資料來源來自單表則該檢視可以進行更新。而如果檢視資料來源來自兩個以上表時這個檢視是不可更新的。但有時候為了操作的方便我們更希望能夠對多表檢視也進行更新。
這時候我們可以通過建立更新觸發器來替代該檢視原有更新以達到多表更新的效果
例如:
3.1 建立測試資料表
--===================================================
--建立測試表
--===================================================
Drop Table t1;
Drop Table t2;
create table t1
( t11 numeric(28),t12 varchar2(20));
create table t2
( t11 numeric(28),t22 varchar2(20));
3.2 多表檢視範例
--===================================================
--建立測試檢視
--===================================================
create Or Replace view t as
select T1.t11 f1 ,T1.t12 f2 ,T2.t22 f3
from T1,T2
Where T1.t11=T2.t11;
3.3 多表檢視觸發器範例
--===================================================
--建立檢視的替代觸發器
--===================================================
Create Or Replace Trigger Trg_InsUpdDel_t
Instead Of Insert or update or delete
on t
for each row
Declare
begin
If Inserting Then
Insert Into t1 (t11,t12) Values (:New.f1,:New.f2);
Insert Into t2 (t11,t22) Values (:New.f1,:New.f3);
elsif Updating Then
Update t1 set t11=:New.f1,t12=:New.f2 where t11=:New.f1;
Update t2 set t11=:New.f1,t22=:New.f3 where t11=:New.f1;
elsif Deleting then
Delete from t1 where t11=:Old.f1;
Delete from t2 where t11=:Old.f1;
End if;
end;
如此即實現多表可更新檢視的定義工作 。
但要注意當檢視進行重新編譯的時候這個觸發器會失效需要重建。
表關聯更新語句:
場景:
表:
AM_APPROVE_LIST_LOG_SAMMY
AM_APPROVE_LIST_SAMMY
更新欄位:
EMP_ID, APPROVER_SEQUENCE, APPROVER_ID
關聯欄位:
EMP_ID, APPROVER_ID
方法一:
UPDATE am_approve_list_log_sammy a
SET (a.emp_id, a.approver_sequence, a.approver_id) =
(SELECT b.emp_id, b.approver_sequence, b.approver_id
FROM am_approve_list_sammy b
WHERE b.emp_id = a.emp_id AND b.approver_id = a.approver_id)
WHERE EXISTS (SELECT 1
FROM am_approve_list_sammy c
WHERE c.emp_id = a.emp_id AND c.approver_id = a.approver_id)
方法二:(10g(含)以上版本才可以使用)
MERGE INTO gt$fm_form_field_import imp
USING fm_form_field fie
ON ( imp.field_name = fie.field_name
AND fie.form_kind = 'BQE.FORM.7'
AND fie.table_name = 'BQEFORM7')
WHEN MATCHED THEN
UPDATE
SET imp.description = fie.description,
imp.is_query = fie.is_query,
imp.is_default_value = fie.is_default_value,
imp.is_query_show = fie.is_query_show,
imp.is_form_show = fie.is_form_show,
imp.is_participant = fie.is_participant,
imp.is_mail_show = fie.is_mail_show
相關文章
- Oracle\MS SQL Server的資料庫多表關聯更新UPDATE與多表更新OracleSQLServer資料庫
- ORACLE多表關聯UPDATE語句Oracle
- ORACLE多表關聯UPDATE 語句Oracle
- Oracle多表關聯更新的方式選擇, Loop or Hash update?OracleOOP
- Oracle多表關聯更新的語法Oracle
- MySQL關聯多表更新的操作MySql
- Mysql跨表更新 多表update sql語句總結MySql
- SQL server 聯表更新SQLServer
- Oracle 多表關聯刪除Oracle
- ms sql server排序SQLServer排序
- sql 多表關聯刪除表資料SQL
- MS SQL SERVER索引優化相關查詢SQLServer索引優化
- Sql server with as update用法SQLServer
- PostgreSQL vs. MS SQL ServerSQLServer
- SQL Server 多表聯合查詢取最新一條資料SQLServer
- SQL優化之多表關聯查詢-案例一SQL優化
- SQL SERVER的UPDATE操作比ORACLE快得多SQLServerOracle
- Update 多個關聯表SQL的寫法SQL
- MS SQL Server儲存過程SQLServer儲存過程
- MS SQL Server和MySQL區別ServerMySql
- 關於使用多表做update的語法
- Sql Server系列:Update語句SQLServer
- MySQL多表關聯查詢MySql
- MySQL 多表關聯刪除MySql
- JPA多表關聯查詢
- MS SQL Server查詢優化方法SQLServer優化
- MS SQL Server的遞迴查詢SQLServer遞迴
- MS Sql server 總結(命令恢復)SQLServer
- MS SQL Server查詢優化方法SQLServer優化
- Sql Server系列:多表連線查詢SQLServer
- Python連線三大資料庫MS Sql Server、Oracle、MySQLPython大資料資料庫ServerOracleMySql
- MySql與Sql Server Update語句MySqlServer
- SQL Server 2022 RTM 最新累積更新:Cumulative Update #13 for SQL Server 2022 RTMSQLServer
- sql查詢更新update selectSQL
- MS SQL Server 資料庫備份方法SQLServer資料庫
- 初識MS SQL Server中的CONVERTSQLServer
- MS SQL Server的遞迴查詢(2)SQLServer遞迴
- MS SQL Server 事務日誌介紹SQLServer