Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: How to update column based on min value of another?

    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?
    Last edited by ozzii; 05-02-10 at 11:50.

  2. #2
    Join Date
    Mar 2007
    Posts
    212
    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?
    Last edited by ozzii; 05-02-10 at 12:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •