{"id":757,"date":"2013-02-12T11:59:29","date_gmt":"2013-02-12T10:59:29","guid":{"rendered":"http:\/\/blog.techmedia.pl\/?p=757"},"modified":"2018-09-05T00:32:04","modified_gmt":"2018-09-04T22:32:04","slug":"mysql-insert-na-postawie-select","status":"publish","type":"post","link":"https:\/\/www.techmedia.pl\/blog\/mysql-insert-na-postawie-select\/","title":{"rendered":"MySQL INSERT i UPDATE na postawie SELECT"},"content":{"rendered":"<p>W MySQL mo\u017cna w prosty spos\u00f3b doda\u0107 nowe wiersze do jednej tabeli na podstawie danych wybranych z innej tabeli. Poni\u017cej przyk\u0142ad zapytania, kt\u00f3re wstawia do tabela1 w pola pole1_t1, pole2_t1, &#8230; wybrane warto\u015bci z tabela2 uwzgl\u0119dniaj\u0105c zadany warunek:<\/p>\n<pre>INSERT INTO `tabela1` (`pole1_t1`,`pole2_t1`,`pole3_t1`,`pole4_t1`,`pole5_t1`) SELECT `pole1_t2`,`pole2_t2`,`pole3_t2`,`pole4_t2`,'dowolna_wartosc' FROM `tabela2` WHERE `pole1_t2`= '2' LIMIT 2;<\/pre>\n<p>Nie we wszystkie pola trzeba wstawia\u0107 warto\u015bci wybrane z innej tabeli. Jak wida\u0107 w powy\u017cszym przyk\u0142adzie mo\u017cna r\u00f3wnie\u017c zamiast \u201adowolna_warto\u015b\u0107\u2019 wpisa\u0107 w\u0142asny ci\u0105g znak\u00f3w.<\/p>\n<p>Oczywi\u015bcie w zapytaniu mo\u017cemy wybiera\u0107 dane z tej samej tabeli, do kt\u00f3rej wstawiamy nowe rekordy.<\/p>\n<p>Na podobnej zasadzie mo\u017cemy r\u00f3wnie\u017c aktualizowa\u0107 rekordy:<\/p>\n<pre><code><span class=\"kwd\">UPDATE<\/span><span class=\"pln\"> tabela1 t1<\/span><span class=\"pun\">,<\/span> <span class=\"pun\">(<\/span><span class=\"kwd\">SELECT<\/span> <span class=\"pun\">*<\/span> <span class=\"kwd\">FROM<\/span><span class=\"pln\"> tabela2 WHERE<\/span><span class=\"pln\"> id<\/span><span class=\"pun\">='<\/span><span class=\"pln\">x'<\/span><span class=\"pun\">)<\/span><span class=\"pln\"> t2 <\/span><span class=\"kwd\">SET<\/span><span class=\"pln\"> t1<\/span><span class=\"pun\">.pole<\/span><span class=\"pln\">1 <\/span><span class=\"pun\">=<\/span><span class=\"pln\"> t2<\/span><span class=\"pun\">.pole<\/span><span class=\"pln\">1 <\/span><span class=\"kwd\">where<\/span><span class=\"pln\"> t1<\/span><span class=\"pun\">.<\/span><span class=\"pln\">id<\/span><span class=\"pun\">=t2.id<\/span><span class=\"pun\">;<\/span><\/code><\/pre>\n<pre>UPDATE tabela1 AS t1 INNER JOIN tabela2 AS t2 ON t1.pole1 = t2.pole2 \r\nSET t1.pole3 = t2.pole4 WHERE t2.pole5 = 'x';<\/pre>\n<p>Ciekawym rozwi\u0105zaniem mo\u017ce by\u0107 aktualizacja pola wraz ze zwi\u0119kszeniem jego warto\u015bci zale\u017cnym od parametr\u00f3w pobranych na podstawie SELECT&#8217;a:<\/p>\n<pre>SET @a =0;\r\nUPDATE tb1 SET pole1 = pole1 + ( @a := @a +1 ) WHERE pole2 = 1 AND pole1 &gt; 2 ORDER BY pole1 ASC;<\/pre>\n<p>Powy\u017cej warto\u015b\u0107 <em><strong>pole1<\/strong><\/em> nast\u0119puj\u0105cych po sobie rekord\u00f3w wed\u0142ug sortowania jest zwi\u0119kszana o kolejne liczby ca\u0142kowite.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>W MySQL mo\u017cna w prosty spos\u00f3b doda\u0107 nowe wiersze do jednej tabeli na podstawie danych wybranych z innej tabeli. Poni\u017cej przyk\u0142ad zapytania, kt\u00f3re wstawia do tabela1 w pola pole1_t1, pole2_t1, &#8230; wybrane warto\u015bci z tabela2 uwzgl\u0119dniaj\u0105c zadany warunek: INSERT INTO `tabela1` (`pole1_t1`,`pole2_t1`,`pole3_t1`,`pole4_t1`,`pole5_t1`) SELECT `pole1_t2`,`pole2_t2`,`pole3_t2`,`pole4_t2`,&#8217;dowolna_wartosc\u2019 FROM `tabela2` WHERE `pole1_t2`= \u201a2\u2019 LIMIT 2; Nie we wszystkie pola [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[15],"tags":[137,48,41],"yst_prominent_words":[],"_links":{"self":[{"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/posts\/757"}],"collection":[{"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/comments?post=757"}],"version-history":[{"count":6,"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/posts\/757\/revisions"}],"predecessor-version":[{"id":2360,"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/posts\/757\/revisions\/2360"}],"wp:attachment":[{"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/media?parent=757"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/categories?post=757"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/tags?post=757"},{"taxonomy":"yst_prominent_words","embeddable":true,"href":"https:\/\/www.techmedia.pl\/blog\/wp-json\/wp\/v2\/yst_prominent_words?post=757"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}