ÃֽŠ°Ô½Ã±Û(DB)
2018.11.28 / 23:41
µÎ°³ÀÇ Table À» Join ÇÏ¿© Update Çϱâ
hanulbit
Ãßõ ¼ö 172
º¸Åë 2°³ ÀÇ Å×ÀÌºí¿¡¼ Á¶ÀÎÀ» ÇÏ¿© ƯÁ¤ Å×À̺íÀÇ Ä÷³À» Update ÇÏ°í ½ÍÀ» ¶§, ´ëºÎºÐ SET Àý¿¡ SubQuery ÇüÅ·Π¹®ÀåÀ» ÀÛ¼ºÇÏ¿©, ƯÁ¤°ªÀ» °¡Á®¿À°Ô ÇÏ¿© Update Çϵµ·Ï ¸¹ÀÌ ÀÛ¼ºÀ» ÇÒ °ÍÀÔ´Ï´Ù.
¿¹¸¦ µé¸é ¾Æ·¡¿Í °°Àº ¹®ÀåÀÌ µÇ°ÚÁÒ?
±×·³ À§ÀÇ ¹®Àå°ú °°Àº Çü½ÄÀ¸·Î table_a ¿¡ µÎ°³ ÀÌ»óÀÇ Ä÷³À» Update ÇÑ´Ù°í »ý°¢Çϸé,
¿Í °°Àº ½ÄÀÇ ¹®ÀåÀ» »ý°¢ÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÇÏÁö¸¸, ¾Æ·¡¿Í °°Àº ¹®ÀåÀº ¾î¶»°Ô µÉ±î¿ä?
SQL¹®À» ¸¹ÀÌ Á¢Çغ¸½Å ºÐÀ̶ó¸é, À§ ¹®ÀåÀÌ ´ë·« ¾î¶² ÀǹÌÀÎÁö ¾Æ½Ç ¼ö ÀÖÀ» °Í ÀÔ´Ï´Ù.
Áï, À§ ¹®Àå°ú °°ÀÌ Updatable Join View ¸¦ »ý°¢ÇÒ ¼ö ÀÖ½À´Ï´Ù.
¹°·Ð ÀÌ°æ¿ì Á¶ÀεǴ 2°³ÀÇ Å×À̺íÀº ¹Ýµå½Ã 1:1 ¶Ç´Â 1:NÀÇ °ü°è¿©¾ß Çϸç, UpdateµÇ´Â Ä÷³ÀÇ Å×À̺íÀº NÂÊ ÁýÇÕÀ̾î¾ß ÇÕ´Ï´Ù.
ÀÌ°ÍÀº 1¿¡ ÇØ´çÇÏ´Â ÁýÇÕÀÎ ºÎ¸ð tableÀÇ Á¶ÀÎ Ä÷³ÀÌ UK ¶Ç´Â PK·Î ¼³Á¤µÇ¾î ÀÖ¾î¾ß µÈ´Ù´Â °ÍÀÔ´Ï´Ù.
À§ÀÇ ¹®Àå¿¡¼ º¸ÀÌ´Â table_a ¿Í table_b°¡ UK ¶Ç´Â PK ·Î ¼³Á¤ÀÌ µÇ¾î ÀÖÁö ¾ÊÀ¸¸é
ORA-01779 cannot modify a column which maps to a non key-preserved table
¿¡·¯¸¦ ¹æ»ýÇÏ¸ç ½ÇÇàµÇÁö ¾Ê½À´Ï´Ù.
ÀÌÂë¿¡¼ Çѹø °í¹ÎÇغÁ¾ß ÇÒ ¹®Á¦°¡,
óÀ½¿¡ ¹®Á¦·Î ´øÁ³´ø µÎ°³ÀÇ TableÀ» Á¶ÀÎÇÏ¿©, ƯÁ¤ tableÀÇ Ä÷³ Çϳª¸¸ ¾÷µ¥ÀÌÆ® ÇÒ°æ¿ì¿£ Set Àý¿¡ Sub-Query ÇüÅ·Π¹®ÀåÀ» ÀÛ¼ºÇصµ Å« ¹®Á¦°¡ µÇÁö ¾ÊÀ» µí ÇÕ´Ï´Ù.
ÇÏÁö¸¸, Ä÷³ÀÌ ¿©·¯°³ÀÏ °æ¿ì¿Í ÇàÀÌ ¹«¼öÈ÷ ¸¹Àº TableÀ» Update ÇÒ °æ¿ì SETÀý¿¡¼ µ¿ÀÏ ¹®ÀåÀ¸·Î ƯÁ¤ Å×À̺íÀ» ¹Ýº¹ÀûÀ¸·Î ½ÇÇàÇؾßÇÏ´Â ·Îµå°¡ ¹ß»ýÇϹǷΠUpdate ÇØ¾ß ÇÏ´Â ÇàÀÌ ¸¹À» °æ¿ì¿£ Updatable Join View¸¦ È°¿ë Çؾ߸¸ ÇÏ´Â ÇÊ¿ä°¡ ÀÖ´Ù.
±×·¯³ª, Updateable Join View ¸¦ È°¿ëÇϱâ À§ÇØ ¸Å¹ø Å×À̺íÀÇ UK ȤÀº PK¸¦ »ý¼ºÇؾ߸¸ ÇØ¾ß Çϳª ¶ó´Â Çʿ伺¿¡ ´ëÇØ »ý°¢ÇØ º¸°Ô µÇ´Âµ¥, ÀϹÝÀûÀ¸·Î´Â UK³ª PK Constraint¸¦ ¼³Á¤Çϱ⠾î·Á¿î °ÍÀÌ Çö½ÇÀÔ´Ï´Ù.
µû¶ó¼, ÀÌ·¯ÇÑ Constraint¸¦ ÇÇÇؼ Updatable Join View¸¦ »ç¿ëÇÒ ¼ö ÀÖµµ·Ï Çϱâ À§ÇØ ÁغñµÈ ¿À¶óŬ ÈùÆ®°¡ Àִµ¥ ±×°ÍÀÌ ¹Ù·Î,
BYPASS_UJVC ÀÔ´Ï´Ù.
Áï, À§¿¡¼ ¼Ò°³ÇÑ ¹®Àå¿¡¼ UK ȤÀº PK°¡ ¼³Á¤µÇÁö ¾Ê¾Æ ¿¡·¯°¡ ¸®ÅÏ µÉ°æ¿ì ¾Æ·¡¿Í °°ÀÌ ÈùÆ®¸¦ Áشٸé Á¤»óÀûÀ¸·Î ½ÇÇàÀÌ µÉ °ÍÀÔ´Ï´Ù.
Ä÷³ÀÌ ¿©·¯°³ ÀÏ °æ¿ì¿¡µµ,
1 2 3 4 | UPDATE table_a a SET column_1 = ( SELECT column_1 FROM table_b b WHERE b. KEY = a. KEY ) |
1 2 3 4 5 6 7 8 9 10 | UPDATE table_a a SET column_1 = ( SELECT column_1 FROM table_b b WHERE b. key = a. key ), column_2 = ( SELECT column_2 FROM table_b b WHERE b. key = a. key ), column_3 = ( SELECT column_3 FROM table_b b WHERE b. key = a. key ) |
1 2 3 4 5 6 7 8 9 | UPDATE ( SELECT a.column_1 AS a_column_1, b.column_1 AS b_column_1 FROM table_a a, table_b b WHERE a. key = b. key ) SET a_column_1 = b_column_1 |
1 2 3 4 5 6 7 8 9 | UPDATE ( SELECT a.column_1 AS a_column_1, b.column_1 AS b_column_1 FROM table_a a, table_b b WHERE a. key = b. key ) SET a_column_1 = b_column_1 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | UPDATE ( SELECT a.column_1 AS a_column_1, b.column_1 AS b_column_1, a.column_2 AS a_column_2, b.column_2 AS b_column_2, a.column_3 AS a_column_3, b.column_3 AS b_column_3 FROM table_a a, table_b b WHERE a. key = b. key ) SET a_column_1 = b_column_1, a_column_2 = b_column_2, a_column_3 = b_column_3 |
À§¿Í °°ÀÌ Ç¥ÇöµÉ ¼ö ÀÖÀ» °Í ÀÔ´Ï´Ù. ÀÌ·¸°Ô µÎ°³ÀÌ»óÀÇ Å×À̺íÀ» Á¶ÀÎÇÏ¿© Update ÇÒ °æ¿ì Updatable Join View ¸¦ È°¿ëÇÏ¿© Update ¹®À» ½ÇÇàÇϸé, ½ÇÇà½Ã°£µµ ´ÜÃà(Æ©´×)µÇ´Â È¿°ú¸¦ º¸½Ç ¼ö ÀÖÀ» µí ÇÕ´Ï´Ù. ¸¶Áö¸·À¸·Î À§¿¡¼ ¼Ò°³ÇÑ ÈùÆ®ÀÇ Àǹ̸¦ Ç®¾îº¸ÀÚ¸é bypass_ujvc : Bypass Updateable Join view Constraints À̶ó´Â ÀÇ¹Ì ÀÔ´Ï´Ù. Ç®¾î¼ Àû¾îº¸´Ï, UJVC ¶ó´Â Àǹ̰¡ ½ï µé¾î¿ÀÁö ¾Ê³ª¿ä?
Ãâó : http://oracleerp.co.kr/bbs/board.php?bo_table=database&wr_id=3