[thelist] mysql error using select to update
jft
jft at worrigee.net
Wed Jan 14 01:49:00 CST 2009
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 small example you can key in to demonstrate the syntax if you wish:
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
I understand this result was what you were looking for.
HTH,
John
> -------Original Message-------
> From: Bob Meetin <bobm at dottedi.biz>
> Subject: [thelist] mysql error using select to update
> Sent: 13 Jan '09 04:40
> This is Joomla, but I think that is immaterial:
> update jos_content set attribs = (select attribs from jos_content where
> id=191) where id=180;
> ERROR 1093 (HY000): You can't specify target table 'jos_content' for
> update in FROM clause
> I am guessing this means that I cannot use the same table for both the
> select and update. If so, what is a workaround?
> Bob Meetin
More information about the thelist
mailing list