How to update column based on min value of another?

ozzii · #1 (**permalink**) 05-02-10, 10:24

Hi

I have a table called order_items:

Code:

CREATE TABLE `tbl_order_items` (
        `order_item_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_id` INT(10) UNSIGNED NOT NULL,
	`pd_id` INT(10) UNSIGNED NOT NULL,
	`num_credits` INT(10) UNSIGNED NOT NULL,
	`exp_date` DATETIME NOT NULL,
        PRIMARY KEY (`order_item_id`),
	INDEX `order_id` (`order_id`),
	INDEX `pd_id` (`pd_id`),
	FOREIGN KEY (`order_id`) REFERENCES `tbl_orders` (`order_id`) ON DELETE CASCADE,
	FOREIGN KEY (`pd_id`) REFERENCES `tbl_products` (`pd_id`)
)

I want update the value of num_credits for the row that has the earliest exp_date.

I try the following but it doesnt work:

Code:

UPDATE tbl_order_items
 SET num_credits = num_credits - 1
 WHERE exp_date = (select min(exp_date) from tbl_order_items)

Any ideas?

ozzii · #2 (**permalink**) 05-02-10, 11:14

ok i figured this one out by using the following:

Code:

UPDATE tbl_order_items
 SET num_credits = num_credits - 1
 order by exp_date ASC LIMIT 1

Which however leads me to another question. Is it possible to to do an update based on values from a derived table in Mysql?

Internet Services

Web Development

Digital Marketing

Consumer Tech