[thelist] mysql error using select to update

jft jft at worrigee.net
Wed Jan 14 01:49:00 CST 2009


You need MySQL's multiple table update syntax (with table aliases):

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.
>  -------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