Well, I have to sleep at some point! :)
I'm not completely sure what you're trying to do, but my best guess is that you're trying to update customer order records (the customer_point field to zero) where the order_time is equal to 1 year less than the last order_time? My guess is that you probably mean to set this field to zero whenever the order_time is *less* than 1 year prior...so for instance, in php you'd write:
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Not connected : ' . mysql_error());
}
$db_selected = mysql_select_db('your_db_name', $link);
if (!$db_selected) {
die ('Can\'t use database: ' . mysql_error());
}
$sql = "
SELECT order_customerid, MAX(order_time) AS 'max_order_time'
FROM cu_order
GROUP BY order_customerid";
$result_outer = mysql_query($sql);
$total_updated=0;
if ($result_outer) {
while ($row = mysql_fetch_assoc($result_outer)) {
$custid = $row['order_customerid'];
$order_time = $row['max_order_time'];
$sql = "
UPDATE customer
SET customer_point=0
WHERE order_customerid=" . $custid . "
AND order_time < DATE_SUB(" . $order_time . ", INTERVAL 1 YEAR)";
$result_inner = mysql_query($sql);
$total_updated += mysql_affected_rows();
}
}
echo 'Total records updated: ' . $total_updated . "\n";
?>
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com