SQL problem

[Ray in HK]

I have a table that have a date field as the pri key.
Now I want to update the record that is last record for each month.

Is it possible to do it in 1 single SQL statement ?

table t
d_Date date PRIMARY KEY,
field1 int

what i want is
add "1" to field1 for all of the following records
select max(d_DATE), DATE_FORMAT(d_D ATE, '%Y%m') AS MONTH from t GROUP BY
MONTH

[Bill Karwin]

Ray in HK wrote:

I have a table that have a date field as the pri key.
Now I want to update the record that is last record for each month.

If you use MySQL 4.1, you have access to subqueries:

UPDATE t
SET field1 = field1 + 1
WHERE d_Date IN (SELECT MAX(d_DATE), DATE_FORMAT(d_D ATE, '%Y%m') AS
MONTH FROM t GROUP BY MONTH)

If you use MySQL 4.0 or older, you can't use subqueries, so it's more
awkward. You may have to get the monthly max dates, store them in a
list in your application code, and then use them to form an "IN( )"
expression with literal values.

For example, in Perl & DBI:

my $dates = $dbh->selectcol_arra yref("SELECT MAX(d_DATE),
DATE_FORMAT(d_D ATE, '%Y%m') AS MONTH FROM t GROUP BY MONTH",
{ Columns=>[1] } );

my $date_list = join(',', map("'$_'", @$dates));

$dbh->do("UPDATE t SET field1 = field1 + 1
WHERE d_Date IN ( $date_list )");

Regards,
Bill K.

[Ray in HK]

thanks

"Bill Karwin" <bi**@karwin.co m> ¦b¶l¥ó news:da******** @enews4.newsguy .com ¤¤
¼¶¼g...

Ray in HK wrote:

I have a table that have a date field as the pri key.
Now I want to update the record that is last record for each month.

If you use MySQL 4.1, you have access to subqueries:

UPDATE t
SET field1 = field1 + 1
WHERE d_Date IN (SELECT MAX(d_DATE), DATE_FORMAT(d_D ATE, '%Y%m') AS
MONTH FROM t GROUP BY MONTH)

If you use MySQL 4.0 or older, you can't use subqueries, so it's more
awkward. You may have to get the monthly max dates, store them in a
list in your application code, and then use them to form an "IN( )"
expression with literal values.

For example, in Perl & DBI:

my $dates = $dbh->selectcol_arra yref("SELECT MAX(d_DATE),
DATE_FORMAT(d_D ATE, '%Y%m') AS MONTH FROM t GROUP BY MONTH",
{ Columns=>[1] } );

my $date_list = join(',', map("'$_'", @$dates));

$dbh->do("UPDATE t SET field1 = field1 + 1
WHERE d_Date IN ( $date_list )");

Regards,
Bill K.