{"id":6815,"date":"2016-05-29T14:46:58","date_gmt":"2016-05-29T06:46:58","guid":{"rendered":"http:\/\/onthe8spot.com\/?p=6815"},"modified":"2016-05-29T14:46:58","modified_gmt":"2016-05-29T06:46:58","slug":"oracle-sql-update-a-table-with-data-from-another-table","status":"publish","type":"post","link":"http:\/\/onthe8spot.com\/index.php\/2016\/05\/29\/oracle-sql-update-a-table-with-data-from-another-table\/","title":{"rendered":"Oracle SQL: Update a table with data from another table"},"content":{"rendered":"

First time I used a correlated update in a script I had to do.<\/p>\n

In oracle SQL, how do I run an sql update<\/strong> query that can update Table 1 with Table 2’s name and desc using the same id?
\nThis is called a correlated update<\/p>\n

UPDATE<\/span> table1 t1\n   <\/span>SET<\/span> (<\/span>name<\/span>,<\/span> desc<\/span>)<\/span> =<\/span> (<\/span>SELECT<\/span> t2<\/span>.<\/span>name<\/span>,<\/span> t2<\/span>.<\/span>desc<\/span>\n                         FROM<\/span> table2 t2\n                        <\/span>WHERE<\/span> t1<\/span>.<\/span>id <\/span>=<\/span> t2<\/span>.<\/span>id<\/span>)<\/span>\n WHERE<\/span> EXISTS<\/span> (<\/span>\n    SELECT<\/span> 1<\/span>\n      FROM<\/span> table2 t2\n     <\/span>WHERE<\/span> t1<\/span>.<\/span>id <\/span>=<\/span> t2<\/span>.<\/span>id <\/span>)<\/span><\/code><\/pre>\n
Assuming the join results in a key-preserved view, you could also<\/p>\n
UPDATE<\/span> (<\/span>SELECT<\/span> t1<\/span>.<\/span>id<\/span>,<\/span>\n               t1<\/span>.<\/span>name name1<\/span>,<\/span>\n               t1<\/span>.<\/span>desc<\/span> desc1<\/span>,<\/span>\n               t2<\/span>.<\/span>name name2<\/span>,<\/span>\n               t2<\/span>.<\/span>desc<\/span> desc2\n          <\/span>FROM<\/span> table1 t1<\/span>,<\/span>\n               table2 t2\n         <\/span>WHERE<\/span> t1<\/span>.<\/span>id <\/span>=<\/span> t2<\/span>.<\/span>id<\/span>)<\/span>\n   SET<\/span> name1 <\/span>=<\/span> name2<\/span>,<\/span>\n       desc1 <\/span>=<\/span> desc2<\/span><\/code><\/pre>\n<\/blockquote>\n
 
\n 
\nSource: Oracle SQL: Update a table with data from another table<\/a><\/em>
\n 
\nGuide to the select form found here<\/a>.
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
First time I used a correlated update in a script I had to do. In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2’s name and desc using the same id? This is called a correlated update UPDATE table1 t1 SET (name, desc) = (SELECT t2.name, … <\/p>\n
Continue reading “Oracle SQL: Update a table with data from another table”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[18],"tags":[],"class_list":["post-6815","post","type-post","status-publish","format-standard","hentry","category-coding"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/posts\/6815","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/comments?post=6815"}],"version-history":[{"count":0,"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/posts\/6815\/revisions"}],"wp:attachment":[{"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/media?parent=6815"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/categories?post=6815"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/onthe8spot.com\/index.php\/wp-json\/wp\/v2\/tags?post=6815"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}