RE: Update with select

[Rolando Edwards]

You are better off with an UPDATE JOIN

UPDATE pdata A,pdata B
SET A.pvalue = B.pvalue
WHERE A.pentrytime = 1175400000
AND   B.pentrytime = 1207022400;

Give it a try !!!

-----Original Message-----
From: Albert E. Whale [mailto:aewhale@stripped]
Sent: Friday, May 02, 2008 4:06 PM
To: mysql@stripped
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?
|
--
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*.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1