1

I am using a MySQL statement to copy data from one table to another.

$sql2 = "INSERT one SELECT id, user_id, name FROM two WHERE user_id='1'";

Both tables are using the same columns: id, user_id and name.

I want to copy the data from table two to table one. When I execute the script I can see that all the data in table two is copied to table one. Also the column id.

I want to keep the id in table one as a unique value. I dont want to copy the id from table two to one. I just want to copy the columns user_id and name and want to auto increment the id in table one.

When I execute the script without id I get a error that says that the colums in one and two are not the same.

Does someone know how I can solve this problem and copy the data without the id column

John
  • 904
  • 8
  • 22
  • 56
  • 1
    'If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement.' - https://dev.mysql.com/doc/refman/8.0/en/insert.html – P.Salmon Jan 12 '19 at 15:14
  • 3
    `insert into one (user_id, name) select user_id, name from two where ...` like so? – Psi Jan 12 '19 at 15:16
  • `user_id='1'` also general MySQL rule if the columns datatype is INT query it as a INT and not a string. If you query it as a string MySQL's autocasting can mess things up and the optimizer might be unable to use indexes if anny. – Raymond Nijland Jan 12 '19 at 15:19
  • And it only works if the id is an auto_increment value, which is stated by the OP – Psi Jan 12 '19 at 15:21

1 Answers1

3

You can use this syntax for your query:

INSERT INTO `one` (`user_id`, `name`) SELECT `user_id`, `name` FROM `two` WHERE `user_id` = 1

The rows that match the WHERE clause in the SELECT statement will be copied to table one.

As the id field is omitted both in the SELECT and INSERT statements then rows created into one will get id as per auto-increment value.

I assumed user_id is a numeric column (of type int, bigint...) so I removed the single quotes around the value 1.

Quotes should be used if the field contains a string (ex. column of type char, varchar...)

Paolo
  • 15,233
  • 27
  • 70
  • 91
  • `user_id='1'` also general MySQL rule if the columns datatype is INT query it as a INT and not a string. If you query it as a string MySQL's autocasting can mess things up and the optimizer might be unable to use indexes if anny. – Raymond Nijland Jan 12 '19 at 15:20
  • I doubt that this actually works. Didn't try it, though. The brackets around the select statement seem a little bit odd to me – Psi Jan 12 '19 at 15:20
  • @RaymondNijland you're right. But the OP query puts `1` between single quotes. We don't know `user_id` column type from the information provided by the OP. – Paolo Jan 12 '19 at 15:25
  • "We don't know user_id column type from the information provided by the OP" that's why i said " **if** the columns datatype is INT query it as a INT and not a string" – Raymond Nijland Jan 12 '19 at 15:27
  • @Psi the round brackets around the select statement are optional but I found they make the query more readable. It works: see https://stackoverflow.com/a/4241646/1579327 – Paolo Jan 12 '19 at 15:28
  • @Paolo I believe you, the syntax just came as a surprise to me, never used it that way – Psi Jan 12 '19 at 15:29