簡介

? ? ? ? 在數據庫中進行增刪改查比較常見，經常會用到update的使用。但是在近期發現update在oracle和postgresql使用卻有一些隱形區別，oracle 在執行update語句的時候set 后面必須跟著1對1的數據關聯而postgresql數據庫卻可以一對多，這就導致數據在被新的時候出現不確定性。

目錄

簡介

案列

oracle

postgresql

解讀

postgresql官網解釋

update語法分享

update對分區表使用的影響

案列

oracle

以下oracle數據庫中的測試代碼

drop table test1;create table test1 (id number ,var varchar2(2) ) ;insert into test1 values(1,2);
select * from test1;update test1 tset var =(select tt.varfrom (select 1 id, '3' varfrom dualunion allselect 1, '4'from dual) ttwhere tt.id = t.id);select * from test1;       

postgresql

以下是postgresql數據庫中的測試代碼

 
drop table if exists test1;create table test1 (id int ,var varchar ) ;select * from test1;insert into test1 values(1,2);update test1 t set var=tt.var from (select 1,generate_series(3,4)::varchar ) as tt(id,var) where  tt.id = t.id;select * from test1;

解讀

在兩個數據庫中都是新建了一張表，其數據只有（1，2），其中id:1是作為set的匹配字段

? ? ? ?

替換新數據表中,其ID為1的值有兩個，在set進行匹配的時候就會發生一對多（數據發散）的情況，此時oracle數據庫就會報錯

select 1 id, '3' varfrom dualunion allselect 1, '4'from dual

一對多（數據發散）的情況，此時oracle數據庫就會報錯。在oracle數據庫中是并不允許的。

但是在postgresql數據庫中卻可以執行，并且主表數據庫被成功更新成了4.

postgresql官網解釋

在官網中，postgresql是支持set后的一對多匹配的，但是具體更新成哪一條數據卻是不可控的。

注：在日常使用postgresql數據庫的時候，注意update語句在使用中，盡可能有唯一主鍵進行關聯。
????????其SET后匹配的數據盡可能保證一對一的關聯。

update語法分享

 WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]SET { column_name = { expression | DEFAULT } |( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |( column_name [, ...] ) = ( sub-SELECT )} [, ...][ FROM from_item [, ...] ][ WHERE condition | WHERE CURRENT OF cursor_name ][ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

?????????此處的RECURSIVE，遞歸語法語法的一個關鍵字，可以對臨時表的數據進行聚合算法。

update對分區表使用的影響

? ? ? ? 在查詢這個一對多進行update執行的時候，官網還對其分區表的分區鍵進行update后，會不會變更其分區進行了解讀：

????????在分區表的情況下，更新一行有可能導致它不再滿足其所在分區的分區約束。此時，如果這個行滿足分區樹中某個其他分區的分區約束，那么這個行會被移動到那個分區。 如果沒有這樣的分區，則會發生錯誤。在后臺，行的移動實際上是一次DELETE操作和一次INSERT操作。

????????在移動的行上的并發UPDATE或DELETE可能會收到序列化失敗錯誤。 假設會話 1 正在分區鍵上執行UPDATE，同時，對可訪問該行的并發會話 2 在此行上執行UPDATE或DELETE操作。 在這種情況下，會話 2 的UPDATE?或?DELETE將檢測行移動并引發序列化失敗錯誤（該錯誤始終返回 SQLSTATE 代碼"40001"）。 如果發生這種情況，應用程序可能希望重試事務。 在通常情況下，表沒有分區或沒有行移動，會話 2 將標識新更新的行，并執行UPDATE/DELETE在此新行版本中。

????????請注意，雖然行可以從本地分區移動到外表分區（如果外數據包裝器支持元組路由），但它們不能從外表分區移動到另一個分區。