-1

How could i resolve this problem while cloning?

Column 'user_id ' in field list is ambiguous... Why?

Here is my query:

 Insert into user_table Select * from user_table 
  where user_id = 21 on duplicate KEY
  UPDATE  user_id =LAST_INSERT_ID(user_id ).
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Nandakumar
  • 1,071
  • 2
  • 11
  • 30
  • 1
    use `aliases` with table names. the sql parser is confused that at what point to which `user_id` column you are referring to. – SajjadHashmi Nov 22 '13 at 09:42
  • 1
    Use `AS` to define the tables and retrieve the columns, for example `select * from table1 as t1 where t1.id=21` – Razorphyn Nov 22 '13 at 09:42
  • Insert into user_table as t1 Select * from user_table as t1 where t1.user_id = 21 on duplicate KEY UPDATE t1.user_id =LAST_INSERT_ID(user_id ).? Am i right? – Nandakumar Nov 22 '13 at 09:47
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as t1 Select * from user_table as t1 where t1.user_id = 21 on duplicate KEY UPDATE at line 1.... Getting error – Nandakumar Nov 22 '13 at 09:49
  • I changed like this ... No errors.. But Zero row inserted...Insert into user_table Select * from user_table as t1 where t1.user_id = 21 on duplicate KEY UPDATE user_id =LAST_INSERT_ID(user_table.user_id ). – Nandakumar Nov 22 '13 at 09:59
  • Possible duplicate of [1052: Column 'id' in field list is ambiguous](http://stackoverflow.com/questions/6638520/1052-column-id-in-field-list-is-ambiguous) – andrewtweber Oct 26 '16 at 19:20

1 Answers1

0

The problem with your query is that you're taking the entire row and trying to insert it into the same table, along with the primary key. What you need to do is to select all the other columns except the primary key and then insert into the table, allowing sql to autoincrement the id like so:

insert into user_table (c1, c2, ...)
    select c1, c2, ...
    from user_table
    where user_id = 1

c1, c2,... are the columns other than your primary key (user_id)

P.S. Sorry for the late reply. Weekends are for partying! ;)

DetourToNirvana
  • 83
  • 3
  • Oh, right. Following your comments on your original post (I don't seem to have enough repo to comment there), it doesn't work like that. Are you trying to pull a particular row from the table and insert it into the same table? If that's what you're trying, there's a different way. Please explain what you're trying to achieve. – DetourToNirvana Nov 22 '13 at 13:08
  • My question is that I have to clone the user_table row and insert all the columns in the user table. User table consits of Primary key user_id. When i clone and insert i am getting the error 'duplicate id'. How can i replace the clonned user_id with last Insert Id – Nandakumar Nov 22 '13 at 13:47
  • I have tried this not working INSERT INTO user_table SELECT * FROM user_table AS p WHERE p.user_id =21 ON DUPLICATE KEY UPDATE user_table.user_id = LAST_INSERT_ID() Working but id not changing – Nandakumar Nov 22 '13 at 14:07
  • Hey, edited the answer after understanding your problem. And sorry for the delay, mate. – DetourToNirvana Nov 24 '13 at 18:24
  • I don't want to mention the columns explicitly dude...Any way thanks for ur commitment... – Nandakumar Nov 25 '13 at 04:35
  • Aah. Well, in that case, here are a couple of references that might lead you to a closure on your problem: [Insert On Duplicate](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html), [Last_insert_id](http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id). I recommend you take the pains to mention all columns other than 'user_id' explicitly. – DetourToNirvana Nov 25 '13 at 09:55
  • I just appreciate your commitment... Ok dude. – Nandakumar Nov 25 '13 at 11:48
  • I just appreciate your commitment... Ok dude...Programmers need comittment u done it... Be in touch – Nandakumar Nov 25 '13 at 11:54