[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