Hi, i was able to try your suggestion. But error seems to show up when
updating already...
+-----------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------------+------+-----+---------+-------+
| datacount | int(255) | | | 0 | |
| office | varchar(255) | | | | |
| filename | varchar(255) | | | | |
| status | enum('0','1') | | | 0 | |
| maxdate | varchar(10) binary | YES | | NULL | |
+-----------+--------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> update dCOPY d inner join tmpupdateme tum on tum.office =
d.office and tum.maxdate = d.dateposted set status = 1;
ERROR 1052: Column: 'status' in field list is ambiguous
mysql>
----- Original Message -----
From: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Tue, 27 Jul 2004 09:17:44 -0400
Subject: Re: using max() on update
To: Louie Miranda <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED]
Here's how I handle these situations. If I need all of the data from
the row that contains the maximum of an unindexed column:
SELECT @maxval := MAX(column_name) FROM tablename;
SELECT * FROM tablename WHERE column_name = @maxval;
For the same thing but for an indexed column
SELECT * FROM tablename ORDER BY column_name DESC LIMIT1;
To get all of the rows that contain the MAX() value of column2 for all
values of column1, I need to use a temporary table:
CREATE TEMPORARY TABLE tmpMax
SELECT column1, MAX(column2) as maxval
FROM tablename
GROUP BY column1;
SELECT *
FROM tablename t
INNER JOIN tmpMax tm
ON tm.column1 = t.column1
AND tm.column2 = t.column2;
Now for your case. You need to update all of the rows that have the
most recent dateposted for each office value.
CREATE TEMPORARY TABLE tmpUpdateMe
SELECT office, MAX(dateposted) as maxdate
FROM dCOPY
GROUP BY office;
UPDATE dCOPY d
INNER JOIN tmpUpdateMe tum
ON tum.office = d.office
AND tum.maxdate = d.dateposted
SET status = 1;
You should only need to wrap the 1 with quotes (like '1') if you are
trying to insert it into a character-based field. You do not need
quotes on any numeric value being assigned to a numeric column.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Louie Miranda <[EMAIL PROTECTED]> wrote on 07/27/2004 03:04:27 AM:
> Just recently post a problem on how can i see all max(dateposted) on
> all of my records by doing this..
>
> select datacount,office,filename,status, max(dateposted) from dCOPY
> group by office;
>
> now, i was wondering if i can use max() on update to update all my
> current records only..
>
> i tried this:
>
> mysql> update dCOPY set status = '1' where max(dateposted);
> ERROR 1111: Invalid use of group function
> mysql>
>
> But as you can see, it returns an error for an invalid group function.
>
> --
> Louie Miranda
> http://www.axishift.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
Louie Miranda
http://www.axishift.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
