0

I want to copy all records of one condition to new entries with other condition. in this example. i want to get all information of xxx=987 and add them with xxx=123 to the same table. So i want to copy some values, but not all into the new record.

INSERT table 
SET 
  pid = UPPER(UUID()),
  xxx = 123,
  (col1, col2) = (SELECT val1, val2 
                  FROM table
                  WHERE xxx = 987)

I tried many things, but it did not workout. any help highly appreciated Best endo

endo.anaconda
  • 2,449
  • 4
  • 29
  • 55

1 Answers1

3

You seem to be looking for an INSERT ... SELECT query :

INSERT INTO table (pid, xxx, val1, val2)
SELECT UPPER(UUID()), 123, val1, val2
FROM table
WHERE xxx = 987;
GMB
  • 216,147
  • 25
  • 84
  • 135
  • but with this solution i need to provide all fields of the table, i want to submit only specific fields, the not mentioned fields will then be filled with default values of table – endo.anaconda Jan 04 '19 at 11:47
  • it works, but i'd rather prefere to use INSERT with SET, since it is a bit more readable, if no better answer comes up, i think i go with your solution – endo.anaconda Jan 04 '19 at 12:33
  • @endoanaconda ... SET is for UPDATE queries, not for INSERTs. – GMB Jan 04 '19 at 12:56
  • AFAIK: INSERT with SET is equivalent to INSERT INTO with VALUES, and accoring to the documentation allowed: https://dev.mysql.com/doc/refman/5.6/en/insert.html – endo.anaconda Jan 04 '19 at 13:36
  • 1
    @endo.anaconda Yes, Insert works with set , but you can't use "select" with that combination, you can do: INSERT INTO table SET xxx='yyy' , but you cant do: INSERT INTO table SET xxx='yyy' , col2 = (SELECT zzz FROM ......) – Tarreq Jan 04 '19 at 14:05