{"id":392,"date":"2012-01-10T08:52:50","date_gmt":"2012-01-10T08:52:50","guid":{"rendered":"http:\/\/brain.dailyon.net\/?p=392"},"modified":"2012-01-10T08:52:50","modified_gmt":"2012-01-10T08:52:50","slug":"oracle-insert-select-update-select-merge-into-%ec%82%ac%ec%9a%a9%ed%95%98%ea%b8%b0","status":"publish","type":"post","link":"https:\/\/blog.goorigo.com\/?p=392","title":{"rendered":"ORACLE : Insert Select , Update Select, Merge Into \uc0ac\uc6a9\ud558\uae30"},"content":{"rendered":"

INSERT SELECT EXAMPLE
\nINSERT INTO table_a
\n( title, name, regdate, register, memberid, categoryid )
\nSELECT title, name, regdate, register, memberid, categoryid
\nFROM table_b
\nWHERE categoryid=10
\n<\/code><\/p>\n

1. Insert Select
\n\ud2b9\uc815 \ud14c\uc774\ube14\uc5d0 \ub2e4\ub978 \ud14c\uc774\ube14\uc758 \ub0b4\uc6a9\uc744 \uac80\uc0c9\ud558\uc5ec \uc5ec\ub7ec\ud589\uc744 \uc785\ub825\ud560\ub54c \uc0ac\uc6a9\ud55c\ub2e4.
\nSQL> INSERT INTO Table1
\nSELECT * FROM Table2;<\/p>\n

2. Update Select
\n\ud2b9\uc815 \ud14c\uc774\ube14\uc5d0 \ub2e4\ub978 \ud14c\uc774\ube14\uc758 \ub0b4\uc6a9\uc744 \uac80\uc0c9\ud558\uc5ec \uc5ec\ub7ec\ud589\uc744 \uc218\uc815\ud55c\ub2e4.
\nSQL> UPDATE Table1 A
\nSET A.aa =
\n(SELECT b.aa
\nFROM Table2 B
\nWHERE B.NO = A.NO)
\nWHERE A.NO = 3;<\/p>\n

3. Merge Into
\n\uc560\ud50c\ub9ac\ucf00\uc774\uc158 \ub85c\uc9c1\uc5d0\uc11c \ub370\uc774\ud130 \uc720\ubba4\ub97c \uac80\uc0ac\ud558\uace0 \uc788\uc73c\uba74 , \ub370\uc774\ud130\ub97c UPDATE \ud558\uace0
\n\uc5c6\uc73c\uba74 INSERT \ud558\ub294 \ud754\ud55c \ud328\ud134\uc744 \uc544\uc8fc \uac04\ub2e8\ud558\uac8c \ucffc\ub9ac \ud55c\ubb38\uc7a5\uc73c\ub85c \ucc98\ub9ac\ud560 \uc218 \uc788\uac8c \ud558\ub294 \ucffc\ub9ac.
\nMERGE
\nINTO
\nInsert \ub098 update \ud560 \ud0c0\uac9f \ud14c\uc774\ube14
\nUSING
\n\ud0c0\ucf13 \ud14c\uc774\ube14\uc5d0 \ub4e4\uc5b4\uac08 \uc18c\uc2a4 \ub370\uc774\ud130 \uc989, \ud14c\uc774\ube14 ,\ubdf0 \uc11c\ube0c\ucffc\ub9ac \uac00 \uc62c \uc218 \uc788\ub2e4.
\nON
\n\uc778\uc11c\ud2b8 \ud560 \uac83\uc778\uc9c0 \uc5c5\ub370\uc774\ud2b8 \ud560\uac83\uc778\uc9c0\ub97c \uacb0\uc815\ud558\ub294 \uc870\uac74 \uc989 , \uc870\uac74\uc774 \ub9de\uc73c\uba74 \uc5c5\ub370\uc774\ud2b8\ubb38 \uc2e4\ud589
\n\uc870\uac74\uc774 \ub9de\uc9c0 \uc54a\uc73c\uba74 \uc778\uc11c\ud2b8 \ubb38\uc744 \uc2e4\ud589\ud55c\ub2e4.
\nWHEN MATCHED THEN
\n\uc870\uac74\uc774 \ub9de\uc558\uc744 \ub54c \ucffc\ub9ac\ubb38
\nWHEN NOT MATCHED THEN
\n\uc870\uac74\uc774 \ub9de\uc9c0 \uc54a\uc558\uc744 \ub54c \ucffc\ub9ac\ubb38<\/p>\n

\uc608\ubb38)
\nMERGE INTO bonuses D
\nUSING (SELECT employee_id, salary, department_id FROM employees
\nWHERE department_id = 80) S
\nON (D.employee_id = S.employee_id)
\nWHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
\nWHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
\nVALUES (S.employee_id, S.salary*0.1);<\/p>\n

\ucc38\uace0\ub85c \uc624\ub77c\ud074 \ub3c4\ud050\uba3c\ud2b8\uc5d0 \uc788\ub294 \uc608\ubb38\uc785\ub2c8\ub2e4.<\/p>\n

[\ucd9c\ucc98] [ORACLE] Insert Select , Update Select, Merge Into \uc0ac\uc6a9\ud558\uae30|\uc791\uc131\uc790 \uae5c\ucc0d\uc774\ub2f7
\nhttp:\/\/blog.naver.com\/PostView.nhn?blogId=prosports0&logNo=80090963292<\/p>\n","protected":false},"excerpt":{"rendered":"

INSERT SELECT EXAMPLE INSERT INTO table_a ( title, name, regdate, register, memberid, categoryid ) SELECT title, name, regdate, register, memberid, categoryid FROM table_b WHERE categoryid=10 1. Insert Select \ud2b9\uc815 \ud14c\uc774\ube14\uc5d0… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/posts\/392"}],"collection":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=392"}],"version-history":[{"count":0,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=\/wp\/v2\/posts\/392\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=392"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=392"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.goorigo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=392"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}