MySQL - Update a table from same table using SELECT

[Jassim Rahma]

Hi, I am trying to update the customers MySQL table using SELECT from the same table but getting:

[Err] 1064 - 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 '(destination_table.title) =

here is the SQL statement:

UPDATE customers destination_table

SET (destination_table.title) =
(

SELECT source_table.title

FROM customers source_table

WHERE source_table.customer_id = 18

)

WHERE destination_table.customer_id = 14;

Thanks, Jassim[^]

Technology News @ www.JassimRahma.com

[Mycroft Holmes]

Not sure about MySQL but you cannot use an alias in an update statement, try removing destination_table and just using customers

Never underestimate the power of human stupidity RAH

[Lost User]

a.) (destination_table.title) should not be in brackets b.) Alias in Update, Looks strange but works :) (at least MSSQL)

UPDATE t
SET t.name= (SELECT s.name FROM CPTest1 s WHERE s.id = 1)
FROM CPTest1 t
WHERE t.id = '4'

[Jassim Rahma]

UPDATE source_table
SET source_table.title = (SELECT destination_table.title FROM customers destination_table WHERE destination_table.customer_id = 18)
FROM customers source_table
WHERE source_table.customer_id = 14

[Err] 1064 - 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 'FROM customers source_table
WHERE source_table.customer_id = 14' at line 21

Technology News @ www.JassimRahma.com

[Lost User]

Thank you for your feedback, maybe I Need to install MySQL to check. Either way I would go as Mycroft Holmes suggested not to use an alias for the "UPDATE" itself.

UPDATE customers
SET customers.title = (SELECT destination_table.title FROM customers destination_table WHERE destination_table.customer_id = 18)
WHERE customers.customer_id = 14

[Edit] I just made a test on "SQL Fiddle". Without Alias I get also an error: "You can't specify target table 'CPTest1' for update in FROM clause" :( [Edit1] Finally I found here mysql update from select - same table - Stack Overflow[^] the answer

update CPTest1 as t,
(
select name from CPTest1 where CPTest1.id = 1) as s
set t.name = s.name
where t.id = 4

Tested on SQL Fiddle[^], MySQL 5.6 :)

[Bernhard Hiller]

Jassim Rahma wrote:

UPDATE customers destination_table

Do you see the un-escaped blank between customers and destination_table? That looks so wrong.

[Mycroft Holmes]

I do believe he is giving customers table and alias of destination_table, apparently MySQL allows aliasing in an update. This was my first reaction as well.

Never underestimate the power of human stupidity RAH