[thelist] mysql update field based upon another

S.M.German smgerman at comcast.net
Sun Jul 27 22:31:29 CDT 2014


> This seems like it should be fairly simple, but anyhow. I am trying to
> synchronize the params field of a category, all the items in that category,
> based upon the params field of an item that is the model. This does not
> work, is not valid mysql:
> 
> update p3h0i_k2_items set params = (select params from p3h0i_k2_items
> where id = 300) where id = 278;
> 
> The params field is a complex field of parameters.  Item ID 300 is the role
> model for the field. I want to first test it by updating the params of a single
> item, above, then if it works, go for the category, something like:
> 
> update p3h0i_k2_items set params = (select params from p3h0i_k2_items
> where id = 300) where catid = 100;
> 
> What is the correct mysql syntax to make this work?
> 
> -Bob

Bob,

Don't know to what extent this applies to mysql, but this is an issue I've hit with other SQL platforms.

The where clause in a subquery will attempt to match criteria from the main query.  Tables and columns should be given aliases specific to the subquery, like:
(select items2.params from p3h0i_k2_items AS items2 where items2 .id = 300)

Some quick searching is saying mysql prefers this type up update to be done by joins, rather than subquery.
http://dba.stackexchange.com/questions/50981/mysql-update-query-with-subquery
http://stackoverflow.com/questions/4268416/sql-update-with-sub-query-that-references-the-same-table-in-mysql


Hope that helps,



Sean






More information about the thelist mailing list