[PHP] newbie: mysql statement
I want to update the most recent record (based on the timestamp in field posted) where the parent field == a specified value (in a table called header). I tried the following mysql statement: UPDATE header WHERE parent = '$this-postid' ORDER by posted SET parent='$this-parent' LIMIT1; but apparently you can't use ORDER in an UPDATE statement. If I take order out, the statement works. That being true (and please correct me if its not) how can I ensure that the newest record is the one being acted upon? Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] newbie: mysql statement
not only that, but you seem to be missing any SET field = value to cause updates to occur. I suspect the reason that the ORDER BY is rejected is be cause the database wants to use an index determined from your where clause to optimize the update process. Keep in mind that no rows are returned to you from an update (which is where the ORDER BY is important). Warren Vail Tools, Metrics Quality Processes (415) 667-7814 Pager (877) 774-9891 215 Fremont 02-658 -Original Message- From: Alexander Ross [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 11:56 AM To: [EMAIL PROTECTED] Subject: [PHP] newbie: mysql statement I want to update the most recent record (based on the timestamp in field posted) where the parent field == a specified value (in a table called header). I tried the following mysql statement: UPDATE header WHERE parent = '$this-postid' ORDER by posted SET parent='$this-parent' LIMIT1; but apparently you can't use ORDER in an UPDATE statement. If I take order out, the statement works. That being true (and please correct me if its not) how can I ensure that the newest record is the one being acted upon? Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] newbie: mysql statement
On Mon, 15 Jul 2002, Alexander Ross wrote: I want to update the most recent record (based on the timestamp in field posted) where the parent field == a specified value (in a table called header). I tried the following mysql statement: UPDATE header WHERE parent = '$this-postid' ORDER by posted SET parent='$this-parent' LIMIT1; but apparently you can't use ORDER in an UPDATE statement. If I take order out, the statement works. That being true (and please correct me if its not) how can I ensure that the newest record is the one being acted upon? Thanks. By using a where clause to identify the postid in the table, something like this perhaps: UPDATE header SET parent='$this-parent' where postid='$this-postid'; -- --- Greg Donald http://destiney.com/public.key --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] newbie: mysql statement
Oops, looks like I missed your Set after the ORDER BY. I've never seen it there, usually I see it immediately after the update. Seem to me some things in SQL are positional, but could be wrong here. Warren Vail Tools, Metrics Quality Processes (415) 667-7814 Pager (877) 774-9891 215 Fremont 02-658 -Original Message- From: Alexander Ross [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 11:56 AM To: [EMAIL PROTECTED] Subject: [PHP] newbie: mysql statement I want to update the most recent record (based on the timestamp in field posted) where the parent field == a specified value (in a table called header). I tried the following mysql statement: UPDATE header WHERE parent = '$this-postid' ORDER by posted SET parent='$this-parent' LIMIT1; but apparently you can't use ORDER in an UPDATE statement. If I take order out, the statement works. That being true (and please correct me if its not) how can I ensure that the newest record is the one being acted upon? Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] newbie: mysql statement
If you could retrieve the max(posted) prior to doing the update you could; SELECT max(posted) from header WHERE parent = '$this-postid' and substitute the value in the following query; Update header SET parent='$this-parent' WHERE parent = '$this-postid' AND posted = $retrieved_posted A subselect would work great here by not in MySQL. Warren Vail Tools, Metrics Quality Processes (415) 667-7814 Pager (877) 774-9891 215 Fremont 02-658 -Original Message- From: Alexander Ross [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 11:56 AM To: [EMAIL PROTECTED] Subject: [PHP] newbie: mysql statement I want to update the most recent record (based on the timestamp in field posted) where the parent field == a specified value (in a table called header). I tried the following mysql statement: UPDATE header WHERE parent = '$this-postid' ORDER by posted SET parent='$this-parent' LIMIT1; but apparently you can't use ORDER in an UPDATE statement. If I take order out, the statement works. That being true (and please correct me if its not) how can I ensure that the newest record is the one being acted upon? Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] newbie: mysql statement
You're right Andrew, got a little ahead of myself :o) Martin Clifford Homepage: http://www.completesource.net Developer's Forums: http://www.completesource.net/forums/ Andrew Brampton [EMAIL PROTECTED] 07/15/02 03:23PM Shouldn't this be UPDATE header SET parent='$this-parent' WHERE posted = max(posted) Otherwise the max(posted) = max(posted) is true for all records (therefore all records get updated) Andrew - Original Message - From: Martin Clifford [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 15, 2002 8:11 PM Subject: Re: [PHP] newbie: mysql statement I would rewrite the query as: UPDATE header SET parent='$this-parent' WHERE max(posted) = max(posted) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] newbie: mysql statement
I like this form better but it's not clear to me if this would only update the row for parent='$this-parent' if it were the last row posted to the table. Depends on whether the max function in the where clause is bound to the max in the table or the max in the sequence set (I tend to think it might be max in the table). Warren Vail Tools, Metrics Quality Processes (415) 667-7814 Pager (877) 774-9891 215 Fremont 02-658 -Original Message- From: Andrew Brampton [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 12:23 PM To: Martin Clifford; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP] newbie: mysql statement Shouldn't this be UPDATE header SET parent='$this-parent' WHERE posted = max(posted) Otherwise the max(posted) = max(posted) is true for all records (therefore all records get updated) Andrew - Original Message - From: Martin Clifford [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 15, 2002 8:11 PM Subject: Re: [PHP] newbie: mysql statement I would rewrite the query as: UPDATE header SET parent='$this-parent' WHERE max(posted) = max(posted) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
