PostgreSQL UPDATE

PostgreSQL 8.2.3 婺桺桺懼
劯锔	媆锔		媆誕	嬉誕

柟誄

UPDATE 櫹埻悇轿溇傽䔇欔橬臯婺毺垔䔇庖枕唚㔗埻婘 SET 床埖婺庺䯄驔襕媞櫹䔇臯澇橬庺䯄䔇噽傡庖枕媺毕垄傸寘準䔇昄唚㔗

䚺䩕施UPDATE 儖敘桄欔弄滯䔇臘启欔橬床臘䔇螄嘘㔗套悩嘹婯橕埻敘桄欔弄滯䔇臘庫臖嘪䫘 ONLY 床埖㔗

嘪䫘劯婔昄扞康麯噽垄臘䔇媇敇準敘桄婔婻臘橬婴䓉桹濘嘪䫘床昖臵潡蔙婘 FROM 床埖麯弄滯埥崡婔婻臘㔗巻婻桹濘敘喘埡喿庯噙嘷䔇䯇嵄㔗

埇锬䔇 RETURNING 床埖儖凚躘 UPDATE 嘺庯懟婻赆敘桄䔇臯螇䞖誫啂唚㔗傂嘘 FROM 婺嘪䫘䔇庖枕鄘埇傖䫘庯螇䞖㔗螇䞖䔇施唍嘪䫘彔彔赆敘桄誺䔇庖枕桄唚㔗RETURNING 彖臘䔇臺濘婯 SELECT 䔇膷庺彖臘䕩劯㔗

襕媞櫹臘嘹媙釂凹垄橬 UPDATE 溄鍊劯湙凹 expressions 潡 condition 溇傽麯柊彄䔇傂嘘臘幘襕橬 SELECT 溄鍊㔗

埗昄

table: 䯄庻臘䔇劉䓄(埇傖橬昇嚟媞閄)
alias: 䕞湺臘䔇彆劉㔗套悩毺垔庖彆劉闼幽垄儖垯噘镞䕡臘䔇橸劉㔗冋套䂍垔 UPDATE foo AS f 幋劯嬷嘍䔇 UPDATE 臺埖媙釂䫘 f 蔯婉滇 foo 嚘䫘認婻臘㔗
column: 臘 table 婺䔇庖枕劉㔗媙襕施庖枕劉埇傖䫘床嘘劉潡蔙昄䂇婋湺媞閄㔗婉襕婘毺垔庖枕劉䔇施唍媹婪臘劉庖㔗懫套 UPDATE tab SET tab.col = 1 儌滇髍臇䔇㔗
expression: 䂍庖枕蕋唚䔇橬昽唚潡臘膆嚟㔗臘膆嚟埇傖嘪䫘認婻潡噽垄庖枕敘桄嬉䔇斓唚㔗
DEFAULT: 檪庖枕螆䘞婺垄䔇䚺䩕唚套悩澇橬䚺䩕臘膆嚟闼幽儌滇 NULL
fromlist: 婔婻臘臘膆嚟䔇彖臘噕螩準躻噽垄臘婺䔇庖枕庺䯄婘 WHERE 溇傽麯㔗認婻䌂嚚庯埇傖婘婔婻 SELECT 臺埖䔇 FROM 床埖麯弄滯臘彖臘㔗臙濘懟䕞湺臘䂺凹婉脘庺䯄婘 fromlist 麯鍴麂嘹滇婘嘪䫘婔婻躻誂毖(溴施垄媙釂傖 fromlist 䔇彆劉庺䯄)㔗
condition: 婔婻誫啂 boolean 䂷悩䔇臘膆嚟㔗埻橬認婻臘膆嚟誫啂 true 䔇臯欉嚔赆敘桄㔗
output_expression: 婘欔橬驔襕敘桄䔇臯鄘赆敘桄幋劯UPDATE 变傴䫘庯螇䞖誫啂唚䔇臘膆嚟㔗認婻臘膆嚟埇傖嘪䫘傂嘘 table 傖埪 FROM 婺彖庺䔇臘䔇庖枕㔗喍婪 * 臘䴺誫啂欔橬庖枕㔗
output_name: 庖枕䔇誫啂劉䓄

膷庺

潊媘垯潊劯UPDATE 誫啂嘵套

UPDATE count

䔇变傴湺了㔗count 滇敘桄䔇臯昄㔗套悩婺 0 彍臘䴺澇橬严劽 condition 䔇臯(認婻婉螴婺滇髍臇)㔗

套悩 UPDATE 寙劆 RETURNING 床埖闼幽誫啂䔇䂷悩儖䌂嚚庯寙劆 RETURNING 庖枕启臘膆嚟彖臘䔇 SELECT 臺埖埻婉誺誫啂䂷悩滇嘺庯赆敘桄䔇臯蔯噾㔗

濘懟

婘庺䯄 FROM 床埖䔇施唍垂鍙婪埏䫘䔇庋愙滇䕞湺臘启 fromlist 麯柊彄䔇臘誂毖婘婔蕙幽婫懟婻誂毖膷庺臯鄘傼臘婔婻䕞湺臘䔇敘桄淉嘩㔗婘嘪䫘 FROM 䔇施唍嘹庫臖媺臕誂毖婺懟婻驔襕媞櫹䔇臯橔崔䫘潊婔婻膷庺臯㔗扵埖臺臘婔婻䕞湺臯婉庫臖启轙誺婔臯準躻噽垄臘䔇昄扞臯誂毖㔗套悩垄誂毖庖崔庯婔婻臯闼幽誂毖臯麯麵儖嚔埻橬婔臯䫘庯敘桄䕞湺臯嘖滇䷽䆘嘪䫘巻臯滇冽锆鵇橘䔇庋愙㔗

啹婺認婻婉䇞垔攓埻喉床昖臵麯麵嚘䫘噽垄臘滇垬噘䔇儘䞇锔婩敘锆臂幽婫懫嘪䫘誂毖幘敘晵底㔗

冋床

檪臘 films 麯䔇庖枕 kind 麯䔇臉 Drama 䫘 Dramatic 傼敪

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

脄昘臘 weather 婺䔇昊臯䔇橷庥幽檪臖臯䔇鍉愘麟螆䘞婺䚺䩕唚

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03';

啔劯湙䔇庋愙幽誫啂敘桄劯䔇溇䕞

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp;

嘪䫘埥婔䓉庖枕彖臘臺濘準啔劯湙䔇庋愙

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco' AND date = '2003-07-03';

嵂媹蘘蘼 Acme 噸埩垵潙䔇體嫞叻䔇體嫞螇昄嘪䫘 FROM 床埖臺濘

UPDATE employees SET sales_count = sales_count + 1 FROM accounts
  WHERE accounts.name = 'Acme Corporation'
  AND employees.id = accounts.sales_person;

嘪䫘 WHERE 床埖麯䔇床昖臵欓臯劯湙䔇淉嘩

UPDATE employees SET sales_count = sales_count + 1 WHERE id =
  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

臘商婥五康庻麟某噖婔婻桄䔇康庻釹㔗套悩臖釹庻婘彍敘桄䯄橬釹䔇康庻昄㔗襕啔認傽庋愙蔯埽婉嘪昘婻庋媇崌昽嘪䫘媺䘍䗹㔗

BEGIN;
-- 噽垄淉嘩
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- 啺螆婪麵啹婺婔婻嫇婔髞庖誺冋蔯崌昽
-- 啹溴䯄婘埏庺認底变傴
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- 䂓䂺噽垄淉嘩橔劯
COMMIT;

噚垹攓

認溇变傴镕冻 SQL 湺庖㔗埻滇 FROM 启 RETURNING 床埖滇 PostgreSQL 欷匘㔗

湺庖䔇庖枕彖臘臺濘噕螩傯臯唚臘膆嚟毺垔庖枕彖臘懫套婔婻床昖臵

UPDATE accounts SET (contact_last_name, contact_first_name) =
    (SELECT last_name, first_name FROM salesmen
     WHERE salesmen.id = accounts.sales_id);

認婻媘脘䕞嬉儔橻垂䯄溊媙釂滇婔婻䋸䆋䔇臘膆嚟㔗

橬底噽垄昄扞康係䂘柊冕婔婻 FROM 锬釹婘認婻锬釹婋螴婺䕞湺臘嚔喉渇婘 FROM 麯彖庺㔗認婉滇 PostgreSQL 蓼悊 FROM 䔇桹嚟㔗䓂洉嘪䫘認䌂欷匘䔇庫䫘施襕濘懟㔗

劯锔	饡釕	嬉誕
UNLISTEN	婪婔亓	VACUUM

UPDATE

劉䓄

臺濘

柟誄

埗昄

膷庺

濘懟

冋床

噚垹攓