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

Improve this question
2

1 Answer 1

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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
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
@endoanaconda ... SET is for UPDATE queries, not for INSERTs.
AFAIK: INSERT with SET is equivalent to INSERT INTO with VALUES, and accoring to the documentation allowed: dev.mysql.com/doc/refman/5.6/en/insert.html
@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 ......)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.