mysqlmysql-insert-idmysql-select-db

Mysql insert with set and select

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

Solution

  • 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;