[thelist] mysql update field based upon another

[Bob Meetin]

One record only.

p3h0i_k2_item is a table of content items, like a newspaper category of headlines, with about 30 or so fields. article IDs are unique. One of the fields called params stores all the display options for the page, author name, images, yadda yadda), JSON format.  Another field stores the category ID (catid).

Article item ID 300 is configured correctly for consistency.  The params field is the role model.  Rather than go into the admin console and open each article (500+) individually and update a few parameters in  params to be the same as ID 300, it would be easier to do a mysql update, updating all the articles' params fields based upon ID 300.

mysql> desc p3h0i_k2_items;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| id                  | int(11)          | NO   | PRI | NULL    | auto_increment |
| title               | varchar(255)     | NO   | MUL | NULL    |                |
| catid               | int(11)          | NO   | MUL | NULL    |                |
...
| params              | text             | NO   |     | NULL    |                |
| metadesc            | text             | NO   |     | NULL    |                |
...
| language            | char(7)          | NO   | MUL | NULL    |                |
+---------------------+------------------+------+-----+---------+----------------+
36 rows in set (0.00 sec)

I'm sure I've done something like this before but could not find the example in my library and googling it returned a myriad of false positives. 90% of  the battle is searching google using the right key phrase.

-B

On 07/25/2014 08:56 AM, Anthony Baratta wrote:
> On 7/25/2014 7:38 AM, Bob Meetin wrote:
>> 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;
>
> Bob...
>
> Does:
>
>     select params from p3h0i_k2_items where id = 300
>
> return more than one record? If so, you can't approach the solution that way.
>
> If you want a true sync, you will need to remove all records for 278, then insert all selected params for 300 assigning them to 278.
>
>     delete from p3h0i_k2_items where id = 278
>
>     insert into p3h0i_k2_items
>     (id, params)
>     select
>           278
>         , params
>     from p3h0i_k2_items
>     where id = 300
>
> Hope that helps.
>


-- 
Bob Meetin
www.dottedi.biz
303-926-0167 (m)