[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