{"id":440,"date":"2010-10-27T23:01:17","date_gmt":"2010-10-27T15:01:17","guid":{"rendered":"http:\/\/rais.my\/blog\/?p=440"},"modified":"2010-10-27T23:03:19","modified_gmt":"2010-10-27T15:03:19","slug":"update-with-subselect-mysql","status":"publish","type":"post","link":"https:\/\/rais.my\/blog\/update-with-subselect-mysql\/","title":{"rendered":"Update with subselect &#8211; MySQL"},"content":{"rendered":"<p>Ada satu kes di mana kita perlu update field &#8216;pic&#8217; kepada nama fail gambar mungkin staffid.jpg. Arrgh takkan nak update satu persatu. Anyway ada satu cara yang boleh digunakan.<\/p>\n<p>Langkah2nya;<br \/>\n1. Create lagi satu table sama seperti table asal beserta data<br \/>\n2. Cuba SQL di bawah.<\/p>\n<p>MySQL<\/p>\n<pre class=\"brush:sql\">\r\nUPDATE tbl1 SET tbl1.pic = \r\n(select CONCAT(tbl1_temp.staffID,'.jpg') FROM tbl1_temp \r\nwhere tbl1.staffID = tbl1_temp.staffID)\r\nWHERE tbl1.staffID = tbl1.staffID \r\n<\/pre>\n<p>Field pic pada table tbl1 akan dikemaskini dengan staffid.jpg (dengan menggunakan fungsi concat) yang diperolehi daripada table tbl1_temp. OK, Sekiranya anda menggunakan pangkalan data lain, SQL di bawah boleh digunakan.<\/p>\n<p>Oracle<\/p>\n<pre class=\"brush:sql\">\r\nUPDATE tbl1 SET tbl1.pic = \r\n(select tbl1_temp.staffID || '.jpg' FROM tbl1_temp \r\nwhere tbl1.staffID = tbl1_temp.staffID)\r\nWHERE tbl1.staffID = tbl1.staffID \r\n<\/pre>\n<p>Sql Server<\/p>\n<pre class=\"brush:sql\">\r\nUPDATE tbl1 SET tbl1.pic = \r\n(select tbl1_temp.staffID + '.jpg') FROM tbl1_temp \r\nwhere tbl1.staffID = tbl1_temp.staffID)\r\nWHERE tbl1.staffID = tbl1.staffID \r\n<\/pre>\n<p>Mungkin ada cara-cara lain&#8230; Harap kawan2 boleh kongsi&#8230;<\/p>\n<p>Selamat mencuba<\/p>\n<p><a href=\"http:\/\/rais.my\" target=\"_blank\">rais.my<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ada satu kes di mana kita perlu update field &#8216;pic&#8217; kepada nama fail gambar mungkin staffid.jpg. Arrgh takkan nak update satu persatu. Anyway ada satu cara yang boleh digunakan. Langkah2nya; 1. Create lagi satu table sama seperti table asal beserta data 2. Cuba SQL di bawah. MySQL UPDATE tbl1 SET tbl1.pic = (select CONCAT(tbl1_temp.staffID,&#8217;.jpg&#8217;) FROM [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cybocfi_hide_featured_image":"","footnotes":""},"categories":[274,74,189],"tags":[275,277,661,652,278,276],"class_list":["post-440","post","type-post","status-publish","format-standard","hentry","category-database","category-mysql","category-sql","tag-concat","tag-insert","tag-mysql","tag-oracle","tag-sql-server","tag-subselect"],"_links":{"self":[{"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/posts\/440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/comments?post=440"}],"version-history":[{"count":3,"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/posts\/440\/revisions"}],"predecessor-version":[{"id":443,"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/posts\/440\/revisions\/443"}],"wp:attachment":[{"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/media?parent=440"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/categories?post=440"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rais.my\/blog\/wp-json\/wp\/v2\/tags?post=440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}