SEO

I am trying to update a field on a record in a table. Here is the
statement I created:

UPDATE pdata SET pvalue = ( SELECT pvalue
FROM pdata
WHERE pentrytime =1207022400 )
WHERE pentrytime =1175400000;

However, I get the following error:

|#1093 - You can't specify target table 'pdata' for update in FROM clause

What can I use to fix this?
|
--
Albert E. Whale, CHS CISA CISSP
Sr. Security, Network, Risk Assessment and Systems Consultant
------------------------------------------------------------------------
ABS Computer Technology, Inc. <http://www.ABS-CompTech.com> - Email,
Internet and Security Consultants
SPAMZapper <http://www.Spam-Zapper.com> - No-JunkMail.com
<http://www.No-JunkMail.com> - *True Spam Elimination*.

> -----Original Message-----
> From: Albert E. Whale [mailto:aewhale@ABS-CompTech.com]
> Sent: Friday, May 02, 2008 4:06 PM
> To: mysql@lists.mysql.com
> Subject: Update with select
>
> I am trying to update a field on a record in a table. Here is the
> statement I created:
>
> UPDATE pdata SET pvalue = ( SELECT pvalue
> FROM pdata
> WHERE pentrytime =1207022400 )
> WHERE pentrytime =1175400000;
>
> However, I get the following error:
>
> |#1093 - You can't specify target table 'pdata' for update in FROM clause
>
> What can I use to fix this?
> |

"Currently, you cannot update a table and select from the same table
in a subquery."
http://dev.mysql.com/doc/refman/5.0/en/update.html

Subqueries in mysql in general are well supported.

The solution already posted is what I would do.

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)