| PostgreSQL 8.1.9ʸ½ñ | ||||
|---|---|---|---|---|
| Á°¤Î¥Ú¡¼¥¸ | ´¬Ìᤷ | ÁáÁ÷¤ê | ¼¡¤Î¥Ú¡¼¥¸ | |
UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM fromlist ]
[ WHERE condition ]UPDATE¤Ï¡¢¾ò·ï¤òËþ¤¿¤¹Á´¤Æ¤Î¹Ô¤Î»ØÄꤷ¤¿Îó¤ÎÃͤòÊѹ¹¤·¤Þ¤¹¡£ SET¶ç¤Ë¤Ï¡¢Êѹ¹¤¹¤ëÎó¤Î¤ß¤ò»ØÄꤹ¤ëɬÍפ¬¤¢¤ê¤Þ¤¹¡£ SET¶ç¤Ë¤ÆÌÀ¼¨Åª¤Ë»ØÄꤵ¤ì¤Ê¤«¤Ã¤¿Îó¤ÎÃͤÏÊѹ¹¤µ¤ì¤Þ¤»¤ó¡£
¥Ç¥Õ¥©¥ë¥È¤Ç¤Ï¡¢UPDATE¤Ï»ØÄꤷ¤¿¥Æ¡¼¥Ö¥ë¤È¤½¤ÎÉû¥Æ¡¼¥Ö¥ë¤Î¹Ô¤ò¹¹¿·¤·¤Þ¤¹¡£ »ØÄꤵ¤ì¤¿¥Æ¡¼¥Ö¥ë¤Î¤ß¤ò¹¹¿·¤·¤¿¤¤¾ì¹ç¤ÏONLY¶ç¤ò»ÈÍѤ·¤Æ¤¯¤À¤µ¤¤¡£
¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î¾¤Î¥Æ¡¼¥Ö¥ë¤Î¾ðÊó¤ò»ÈÍѤ·¤Æ¥Æ¡¼¥Ö¥ë¤òÊѹ¹¤¹¤ë¤Ë¤Ï¡¢2¤Ä¤ÎÊýË¡¤¬¤¢¤ê¤Þ¤¹¡£ 1¤Ä¤ÏÉûÌ䤤¹ç¤ï¤»¤ò»ÈÍѤ¹¤ëÊýË¡¡¢¤â¤¦1¤Ä¤ÏFROM¶ç¤ÇÄɲ乤ë¥Æ¡¼¥Ö¥ë¤ò»ØÄꤹ¤ëÊýË¡¤Ç¤¹¡£ ¤É¤Á¤é¤ÎÊýË¡¤¬Å¬ÀڤǤ¢¤ë¤«¤Ï¾õ¶·¼¡Âè¤Ç¤¹¡£
¥Æ¡¼¥Ö¥ë¤òÊѹ¹¤¹¤ë¤Ë¤ÏUPDATE¸¢¸Â¤¬É¬ÍפǤ¹¡£ ¤µ¤é¤Ë¡¢expression¤äcondition¤ÇÆɤ߹þ¤à¥Æ¡¼¥Ö¥ë¤ËÂФ¹¤ëSELECT¸¢¸Â¤âɬÍפˤʤê¤Þ¤¹¡£
¹¹¿·ÂоݤΥơ¼¥Ö¥ë¤Î̾Á°¤Ç¤¹¡Ê¥¹¥¡¼¥Þ½¤¾þ̾¤Ç¤â²Ä¡Ë¡£
tableÆâ¤ÎÎó̾¤Ç¤¹¡£ ɬÍפ˱þ¤¸¤Æ¡¢Îó̾¤òÉû¥Õ¥£¡¼¥ë¥É̾¤äÇÛÎó¤Î»Ø¼¨»Ò¤Ç½¤¾þ¤¹¤ë¤³¤È¤â²Äǽ¤Ç¤¹¡£
Îó¤ËÂåÆþ¤¹¤ë¼°¤Ç¤¹¡£ ¤³¤Î¼°¤Ç¤Ï¡¢¥Æ¡¼¥Ö¥ëÆâ¤ÎÂоÝÎó¤ä¤½¤Î¾¤ÎÎó¤ÎÊѹ¹Á°¤ÎÃͤò»ÈÍѤ¹¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£
Îó¤Ë¥Ç¥Õ¥©¥ë¥ÈÃͤòÀßÄꤷ¤Þ¤¹ ¡Ê¥Ç¥Õ¥©¥ë¥È¼°¤¬³ä¤êÅö¤Æ¤é¤ì¤Æ¤¤¤Ê¤¤¾ì¹ç¤ÏNULL¤Ë¤Ê¤ê¤Þ¤¹¡Ë¡£
WHERE¾ò·ï¤ä¹¹¿·ÍѤμ°¤Ë¤ª¤¤¤Æ¡¢Â¾¤Î¥Æ¡¼¥Ö¥ë¤ÎÎó¤ò»ØÄꤹ¤ë¤¿¤á¤Ë»ÈÍѤ¹¤ë¥Æ¡¼¥Ö¥ë¼°¤Î½¸¹ç¤Ç¤¹¡£ ¤³¤ì¤ÏSELECTʸ¤ÎFROM¶ç¤Ç»ØÄꤹ¤ë¥Æ¡¼¥Ö¥ë¤Î¥ê¥¹¥È¤Ë»÷¤Æ¤¤¤Þ¤¹¡£ ¼«¸Ê·ë¹ç¤ò¹Ô¤¦¾ì¹ç¤ò½ü¤¡¢fromlist¤Ë¹¹¿·ÂоݤΥơ¼¥Ö¥ë¤ò´Þ¤á¤ëɬÍפϤ¢¤ê¤Þ¤»¤ó ¡Ê¼«¸Ê·ë¹ç¤ò¹Ô¤¦¾ì¹ç¤Ï¡¢fromlistÆâ¤Ç¹¹¿·ÂоݤΥơ¼¥Ö¥ë¤È¤½¤ÎÊÌ̾¤ò»ØÄꤷ¤Æ¤ª¤¯É¬Íפ¬¤¢¤ê¤Þ¤¹¡Ë¡£
boolean·¿¤ÎÃͤòÊÖ¤¹¼°¤Ç¤¹¡£ ¤³¤Î¼°¤¬true¤òÊÖ¤¹¹Ô¤Î¤ß¤¬¹¹¿·¤µ¤ì¤Þ¤¹¡£
Àµ¾ï¤Ë½èÍý¤¬½ª¤ï¤ë¤È¡¢UPDATE¥³¥Þ¥ó¥É¤Ï°Ê²¼¤Î·Á¼°¤Î¥³¥Þ¥ó¥É¥¿¥°¤òÊÖ¤·¤Þ¤¹¡£
UPDATE count
count¤Ï¹¹¿·¤µ¤ì¤¿¹Ô¿ô¤ò°ÕÌ£¤·¤Þ¤¹¡£ count¤¬0¤Î¾ì¹ç¤Ïcondition¤Ë°ìÃפ¹¤ë¹Ô¤¬¤Ê¤«¤Ã¤¿¤³¤È¤ò°ÕÌ£¤·¤Þ¤¹ ¡Ê¤³¤ì¤Ï¥¨¥é¡¼¤È¤Ï¤ß¤Ê¤µ¤ì¤Þ¤»¤ó¡Ë¡£
FROM¶ç¤¬Â¸ºß¤¹¤ë¾ì¹ç¡¢´ðËÜŪ¤Ë¡¢Âоݥơ¼¥Ö¥ë¤Èfromlist¤Ç»ØÄꤵ¤ì¤¿¥Æ¡¼¥Ö¥ë¤¬·ë¹ç¤µ¤ì¡¢¤³¤Î·ë¹ç¤Î½ÐÎϹԤ¬Âоݥơ¼¥Ö¥ë¤Î¹¹¿·Áàºî¤Î·ë²Ì¤È¤Ê¤ê¤Þ¤¹¡£ FROM¶ç¤ò»ÈÍѤ¹¤ë¾ì¹ç¡¢¹¹¿·Âоݥơ¼¥Ö¥ë¤Î1¹Ô¤ËÂФ·¤Æ¡¢·ë¹ç·ë²Ì¤¬Ê£¿ô¹Ô¤Ë¤Ê¤é¤Ê¤¤¤è¤¦¤ËÃí°Õ¤·¤Æ¤¯¤À¤µ¤¤¡£ ¸À¤¤´¹¤¨¤ë¤È¡¢Âоݥơ¼¥Ö¥ë¤Î¸Ä¡¹¤Î¹Ô¤Ï¡¢Â¾¥Æ¡¼¥Ö¥ë¤ÎÊ£¿ô¤Î¹Ô¤È·ë¹ç¤¹¤Ù¤¤Ç¤Ï¤¢¤ê¤Þ¤»¤ó¡£ ·ë¹ç·ë²Ì¤¬Ê£¿ô¹Ô¤Ë¤Ê¤Ã¤¿¾ì¹ç¡¢ÂоݹԤι¹¿·¤Ë¤Ï·ë¹ç·ë²Ì¤Î¤¤¤º¤ì¤«1¹Ô¤Î¤ß¤¬»ÈÍѤµ¤ì¤Þ¤¹¤¬¡¢¤É¤Î¹Ô¤¬»ÈÍѤµ¤ì¤ë¤«¤Ï´Êñ¤Ë¤Ïͽ¬¤Ç¤¤Þ¤»¤ó¡£
¤³¤Î¤è¤¦¤ÊÌäÂ꤬¤¢¤ë¤¿¤á¡¢Â¾¥Æ¡¼¥Ö¥ë¤Î»²¾È¤ÏÉûÌ䤤¹ç¤ï¤»Æâ¤Î¤ß¤Ëα¤á¤Æ¤ª¤¤¤¿Êý¤¬¤è¤ê°ÂÁ´¤Ç¤¹¡Ê¤¿¤À¤·¡¢·ë¹ç¤è¤ê¤â²ÄÆÉÀ¤ä¼Â¹Ô®ÅÙ¤ÏÄã²¼¤·¤Þ¤¹¡Ë¡£
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';
FROM¶ç¹½Ê¸¤ò»ÈÍѤ·¤Æ¡¢¥¢¥¯¥á¼Ò¤ò¸ÜµÒ¤È¤¹¤ë¥»¡¼¥ë¥¹¥Þ¥ó¤Î¥»¡¼¥ë¥¹¥«¥¦¥ó¥È¤ò1Áý²Ã¤µ¤»¤Þ¤¹¡£
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¶ç¤ÏPostgreSQL¤Î³ÈÄ¥¤Ç¤¹¡£
¾¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¥·¥¹¥Æ¥à¤Ë¤Ï¡¢FROM¥ª¥×¥·¥ç¥óÆâ¤Ç¡¢Âоݥơ¼¥Ö¥ë¤¬ºÆÅÙ»ØÄꤵ¤ì¤ë¤³¤È¤òÁ°Äó¤È¤·¤ÆÆ°ºî¤¹¤ë¤â¤Î¤â¤¢¤ê¤Þ¤¹¡£ ¤³¤ì¤ÏPostgreSQL¤Ë¤ª¤±¤ëFROM¤Î²ò¼áÊýË¡¤È¤Ï°Û¤Ê¤ê¤Þ¤¹¡£ FROM³ÈÄ¥µ¡Ç½¤ò»ÈÍѤ¹¤ë¥¢¥×¥ê¥±¡¼¥·¥ç¥ó¤ò°Ü¿¢¤¹¤ë»þ¤ÏÃí°Õ¤·¤Æ¤¯¤À¤µ¤¤¡£
| Á°¤Î¥Ú¡¼¥¸ | ¥Û¡¼¥à | ¼¡¤Î¥Ú¡¼¥¸ |
| UNLISTEN | ¾å¤ËÌá¤ë | VACUUM |