[thelist] mysql error using select to update
Jack Timmons
jorachim at gmail.com
Mon Jan 12 12:50:54 CST 2009
On Mon, Jan 12, 2009 at 12:40 PM, Bob Meetin <bobm at dottedi.biz> wrote:
> 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
>
This is a rough outline of what I'd do:
<?
$arr = mysql_fetch_assoc(mysql_query("SELECT attrs FROM table WHERE id =
#"));
$terms = array();
foreach($arr as $k=>$v)
$terms[] = "$k = '$v'";
mysql_query("UPDATE table SET ".implode(", ",$terms)." WHERE id = #");
?>
But I'm pretty lazy right now, and can't afford to look up SQL workarounds.
--
-Jack Timmons
http://www.trotlc.com
Twitter: @jorachim
More information about the thelist
mailing list