MySQL - complex INSERT ... SELECT on the same table [MySQL 5.051]
I am trying to insert a new row into my table that contains the same data as the one I am trying to select from the same table, but with a different user_id
and no fixed value for auto_id
, since this is an auto_increment field and setting ti
to NOW (). Below is my mock query where "1" is new user_id
. I tried many options but I am still stuck, anyone who can help me turn this into a working query.
INSERT INTO `lins` ( `user_id` , `ad` , `ke` , `se` , `la` , `ra` , `ty` , `en` , `si` , `mo` , `ti` , `de` , `re` , `ti` ) (
SELECT '1', `ad` , `ke` , `se` , `la` , `ra` , `ty` , `en` , `si` , `mo` , `ti` , `de` , `re` , NOW( )
FROM `lins`
WHERE autoid = '4'
AND user_id = '2'
)
Thanks for taking the time to help me!
I just tried it and it works for me.
Are you missing a column name that matches " ke
"?
I'm assuming the actual column names in your table are not just two letters. For example. you have two columns named " ti
" in the query you are showing, so I am assuming you edited that with longer names.
source to share
Presumably, you know that "WHERE autoid = 4" is sufficient if it is a unique autoid. And if you intend to match the quotes with whole numbers (1, 4, and 2), and they are numeric, you've created implicit casts; in a WHERE clause, which will disable the optimizer's ability to use indexes on the resulting integer values.
Also, using unnecessary (and easily misleading) back ticks was at the root of at least the same question here.
source to share