RE: [PHP] newbie: mysql statement

2002-07-15 Thread Vail, Warren

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




Re: [PHP] newbie: mysql statement

2002-07-15 Thread Martin Clifford

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

2002-07-15 Thread Andrew Brampton

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




RE: [PHP] newbie: mysql statement

2002-07-15 Thread Vail, Warren

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

2002-07-15 Thread Martin Clifford

I would rewrite the query as:

"UPDATE header SET parent='$this->parent' WHERE max(posted) = max(posted)"

I would think this to work, though I don't have the facilities to test it, and have 
never needed to do a query such as this.  In theory, it should update ONLY the row 
where the maximum value of the posted column equals itself, thereby being the latest 
date.  Just an idea.

Martin Clifford
Homepage: http://www.completesource.net
Developer's Forums: http://www.completesource.net/forums/


>>> "Alexander Ross" <[EMAIL PROTECTED]> 07/15/02 02:56PM >>>
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

2002-07-15 Thread Vail, Warren

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

2002-07-15 Thread Greg Donald

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

2002-07-15 Thread Vail, Warren

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




[PHP] newbie: mysql statement

2002-07-15 Thread Alexander Ross

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