[thelist] mysql error using select to update - resolved

[Bob Meetin]

jft wrote:
> Bob,
>
> You need MySQL's multiple table update syntax (with table aliases):
>  http://dev.mysql.com/doc/refman/5.0/en/update.html 
>
> Try this:
> create table TEST (ID integer primary key, attribs varchar(10)) ;
> insert into TEST values (1,"attribOne");
> insert into TEST values (2,"attribTwo");
> select * from TEST;
> ID     attribs
> ====   =======
>   1    attribOne
>   2    attribTwo
> update TEST a, TEST b set a.attribs = b.attribs where a.ID = 1 and b.ID = 2;
> select * from TEST;
> ID     attribs
> ====   =======
>   1    attribTwo
>   2    attribTwo
> HTH,
> John
Okay with slight changes this did it.  The purpose of the query is to 
synchronize the attributes of the parameters in Joomla's 
jos_content.attribs' field.  Joomla provides an interface to 
administrators to update about a dozen parameters which are all stored 
in a single field.  Doing a simple update by using copy/paste in MySQL 
was likely to get messy so the idea is to set up a single content item 
and update other content items from the mysql command line.  What Jack 
said probably would have worked in PHP (thanks) but the goal was command 
line.  Although I didn't really want to create a temp table, the 
variable method failed, for whatever mysterious reason.  Paul gets a 
kudos here for dueling with Rudy.  I'm also adding this to my blog as 
well, but without further ado...
--------------------------------------------------------


To update/synchronize the content parameters of jos_content.attribs by 
running a series of command line MySQL statements:

1) Start by creating a content item i Joomla articles with the 
parameters you want; note the article ID.

2) Using whatever mechanism at your disposal, access the database at the 
MySQL command line (your login, password, database name will be 
required. In this case we will assume 118 to be the ID we want to mimic 
and 110 as the ID of the content item we want to update:

% mysql -u<username> -p<password> database <cr>

3) Take a look at the attributes for both the role model and the ID to 
mimic, if Joomla something like:

mysql> select attribs from jos_content where id=118;
-------------------+
| show_title=0
link_titles=0
show_intro=0
show_section=0
link_section=0
show_category=0
link_category=0
show_vote=0
show_author=0
show_create_date=0
show_modify_date=0
show_pdf_icon=0
show_print_icon=0
show_email_icon=0
language=
keyref=
readmore= |
+---------------
mysql> select attribs from jos_content where id=110;
+---------+
| attribs |
+---------+
|         |
+---------+
1 row in set (0.00 sec)

4) Create a test table to handle attributes and inser into it the values 
from the role model:

mysql> create table TEST (ID integer primary key, attribs text);
mysql> insert into TEST select id, attribs from jos_content where id="118";

5) Verify that the insert query worked:

mysql> select * from TEST;
+-----+---------------------------------------+
| ID  | 
attribs                                                                                                                                                                                                                                               
|
+-----+-----+---------------------------------+
| 118 | show_title=0
link_titles=0
show_intro=0
show_section=0
link_section=0
show_category=0
link_category=0
show_vote=0
show_author=0
show_create_date=0
show_modify_date=0
show_pdf_icon=0
show_print_icon=0
show_email_icon=0
language=
keyref=
readmore= |
+-----+-----------------------------------------+
1 row in set (0.00 sec)
mysql>

6) run the query to update ID 110:

mysql> update jos_content set attribs = (select attribs from TEST where 
id="118") where id=110;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>
 
7) To be sure, check the results for ID 110

mysql> select attribs from jos_content where id=110;
-------------------+
| show_title=0
link_titles=0
show_intro=0
show_section=0
link_section=0
show_category=0
link_category=0
show_vote=0
show_author=0
show_create_date=0
show_modify_date=0
show_pdf_icon=0
show_print_icon=0
show_email_icon=0
language=
keyref=
readmore= |
+---------------

8) If you want to update of range of ID's in your database you can do so 
by adjusting the where clause as in these examples:

mysql> update jos_content set attribs = (select attribs from TEST where 
id="118") where id > 50;
mysql> update jos_content set attribs = (select attribs from TEST where 
id="118") where id in (1,3,5,6,7,8);

9) Remove the temporary table if you're done with it.

mysql> drop table TEST;
Current database: matthelm_jvm15
Query OK, 0 rows affected (0.06 sec)
mysql>

----------------------------------------------------------------