[thelist] mysql error using select to update - resolved
Bob Meetin
bobm at dottedi.biz
Thu Jan 15 09:44:21 CST 2009
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>
----------------------------------------------------------------
More information about the thelist
mailing list