[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

MySQL Update Query using SELECT Statement

Hi

I am attempting to update a number of rows in a MySQL table with values from other rows within the same table and my attempts have failed. My attempt is below however it delivers the error message listed below the query.

UPDATE `Table` set `field_value` =
(SELECT `field_value` FROM `Table` WHERE `field_name` = 'xyz')
WHERE `field_name` = 'abc'

#1093 - You can't specify target table 'Table' for update in FROM clause

Basically I am attempting to use the Select Query to create the variable to use in the update Query.

Is this at all possible?

FLOG51
0
FLOG51
Asked:
FLOG51
4 Solutions
 
rajvjaCommented:
Try

update src set fldname=tab.fldname from mytable src
inner join mytable tab on src.idfield=tab.idfield
and tab.fldname='xyz'
where src.fldname='abc'

Hope this helps
0
 
a_bCommented:
0
 
tigin44Commented:
try this

UPDATE `Table` t1, `Table` t2  
set t1.`field_value` = t2.`field_value`
WHERE t1.`field_name` = 'abc'
  AND t1.`field_name` = 'xyz'
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
a_bCommented:
Also -

"In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/mysql/en/UPDATE.html

http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause
0
 
FLOG51Author Commented:
Hi rajvja

Am I correct in assuming that you have used two tables in this query i.e. "src" and "tab"

I am only using one table which has the data I wish to use to update values in the same Table.

Thanks
FLOG51
0
 
FLOG51Author Commented:
Thanks for the attempted help guys, as 'a_b' has stated this is possible from multiple tables but not from one.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now