2

I don't know if MySQL (or any DB for that matter) can do this, but I'm assuming it can be done.

I have a table, with multiple fields. One of these fields tracks the total number of available 'items', and another holds how many are currently in use.

Is it possible to validate incoming data in an UPDATE statement, such that the UPDATE will fail if the number of items in use would become greater than the total available? IE can I add numerical limits to a field based on the contents of another field?

Improve this question
2
  • 3
    You're basically looking for a BEFORE UPDATE trigger to validate the value. Commented Jan 25, 2013 at 7:02
  • 2
    Please refer this and this if you need help creating triggers. Commented Jan 25, 2013 at 7:15

2 Answers 2

Reset to default
5

Say you have a couple tables:

Items
------------
ItemID
NumAvailable
-------------

Checkout
-----------
UserID
ItemID
-----------

You could create a trigger that sums the ItemID and compares to the NumAvailable for that particular item. It would look something like this (may have errors, general idea presented only :) . The method for error gleaned from here, there may be a better way available):

CREATE TRIGGER check_available 
BEFORE INSERT ON Checkout 
FOR EACH ROW 
BEGIN
  SELECT IF (COUNT(new.ItemID) > Items.NumAvailable) THEN
    DECLARE dummy INT;
        SELECT 'No more items to check out!' INTO dummy 
  FROM new NATURAL JOIN Items WHERE NEW.ItemID = Items.ItemID
  END IF;
END
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yep, triggers look to be exactly what I was looking for. It's a shame aborting updates is so hacky in SQL, but that can't be helped, I guess. Thanks.
-2 
update tableName set useItemColumnName= case when totalItemColumnName>=amount then amount else useItemColumnName end .

Note: amount is your updatable variable. It may be 1, 2, 3...

Improve this answer

3 Comments

That's not quite what I want. If I understand it correctly. If the new amount is greater than the allowable maximum, I want the update to fail.
yes update will fail. see my example below. I have used in my db table
update test set use_pro= case when total_pro>=1 then 1 else use_pro end

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.