This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Copy field in the same table

 
Jeppe Sommer
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi.

I want to copy one field in the same table. Expect we have some "productDescriptions" for companyID = 249. How can we copy the "productDescriptions" to companyID = 248?

The code I tried which doesn' t work:

The error in mySQL for the above SQL query is:
Error Code : 1093
You can't specify target table 'product' for update in FROM clause
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Although I know this update would work in Oracle, maybe some databases might not support this kind of update (they'd deadlock themselves).

You might try to use an alias for one of the instances, as in just in case this would be a name resolution problem - but I doubt it would help.
 
Jeppe Sommer
Ranch Hand
Posts: 270
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In mySQL 5 I still get the error
Error Code : 1093
You can't specify target table 'product' for update in FROM clause


Martin Vajsar wrote:Although I know this update would work in Oracle, maybe some databases might not support this kind of update (they'd deadlock themselves).

You might try to use an alias for one of the instances, as in just in case this would be a name resolution problem - but I doubt it would help.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know MySQL at all, but out of curiosity I tried to search. This documentation page seems to state that MySQL does not allow you to update table which you select from (see the last bullet).

The first comment on that page incidentally deals with the same problem, with a workaround using a variable.
 
Keith Rainey
Ranch Hand
Posts: 66
Android Eclipse IDE IntelliJ IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are missing the part where you need to relate the subquery back to the Update query. Try this:


update product set productDescription = (select productDescription from product p2 where product.productId = p2.productId and companyID=249) where companyID=248



 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic