Jon Maz wrote:
> Hi Jerry,
>
> Your solution is certainly nice and simple & a big improvement on mine, but
> I just ran into this error:
>
> UPDATE articles_categories
> SET categoryId=7
> WHERE categoryId=8 AND
> articleId NOT IN (SELECT articleId
> FROM articles_categories
> WHERE categoryId = 7);
>
> Error Code : 1093
> You can't specify target table 'articles_categories' for update in FROM
> clause
>
> Any ideas?
>
> TIA,
>
> JON
>
>
>
>
>
Sorry, that's right. MySQL doesn't allow you to update the table in the
subselect, either. Sometimes I hate the restrictions in MySQL! (I use DB2 for
non-web work - much more mature - but much more expensive).
The only other way I can think of doing this is to temporarily store the info in
your program then either delete or update, as appropriate. For instance:
$result = mysql_query('SELECT articleId ' .
'FROM articles_categories ' .
"WHERE categoryId = $newcategoryId");
$list = "";
while ($data = mysql_fetch_array($result)) {
if ($list != '')
$list .= ', ';
$list .= $data[0];
}
mysql_free_result($result);
$result = mysql_query('DELETE FROM articles_category ' .
"WHERE article_id IN ($list)";
$result = mysql_query('UPDATE articles_category ' .
"SET categoryId = $newcategoryId " .
"WHERE categoryId = $oldcategoryId");
Of course, use appropriate error checking.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================