0

How do I copy content from one table with a where clause to the same table to another where clause?

So the Select Query would be: SELECT * FROM items WHERE countryNr=5;

I want now to insert all data from the above query into for example countryNr=1.

So countryNr=5 and CountryNr=1 should habe in the end the same data.

patrick_
  • 156
  • 2
  • 12
  • 2
    Possible duplicate of [How to copy a row and insert in same table with a autoincrement field in MySQL?](http://stackoverflow.com/questions/9156340/how-to-copy-a-row-and-insert-in-same-table-with-a-autoincrement-field-in-mysql) – raumkrieger Nov 11 '16 at 15:17
  • Wouldn't you have to spell out all the values for the select (instead of using *) and simply put 1 in place of the field name for countryNR in the select? or dup of http://stackoverflow.com/questions/5253302/insert-into-select-for-all-mysql-columns maybe? – xQbert Nov 11 '16 at 15:21

2 Answers2

1

What you have described is more like an UPDATE than INSERT... in that case your query could be something like this:

update item
set (field1, field2) = (select field1, field2 from item where countrNR = 5)
where countrNR = 1
hbourchi
  • 309
  • 2
  • 8
  • I think that should work. I will try thhis later. I tried this before but it did not really worked as i thought. INSERT INTO ts_items (BtnNr, ScreenNr, DepartNr, FunctionNr, TypeNr, ItemNr, BtnText, BonText, Qty, Price, MWStSatzNr, Enabled, Visible, TextColor, LastUpdate, VirtualKeyNr) SELECT BtnNr, ScreenNr, DepartNr, FunctionNr, TypeNr, ItemNr, BtnText, BonText, Qty, Price, MWStSatzNr, Enabled, Visible, TextColor, LastUpdate, VirtualKeyNr FROM ts_items WHERE FilialNr=5 – patrick_ Nov 11 '16 at 15:54
0

I'm not sure I understand the question but something like... mySQL docs for insert into select

Insert into items Select Field1, Field2, Field3, Field4..., 1 as countryNR 
from items where countrNR = 5
xQbert
  • 34,733
  • 2
  • 41
  • 62