Update statement to a maximum of 100.. SQL

[Akumos]

Is it possible to update a column to a maximum of 100?

For example, every day I want to add 5 to a column, but I never want the value to exceed 100... So if its at 97, I don't want it to go to 102, just 100.

So I have:
UPDATE table SET column = (column + 5);

I'm hoping there is something easy to add to the end...

 

[Akumos]

Update:
I just found the CHECK constraint, which would work perfectly but I am using PHPMyAdmin so mySQL doesn't support I don't think.. is there any other way to implement this?

 

[hellrazor]

Don't use something so horrendously named as PHPMyAdmin.

 

[Akumos]

lol - but it's simple and makes everything look pretty

 

[Aquinus]

For example, every day I want to add 5 to a column, but I never want the value to exceed 100... So if its at 97, I don't want it to go to 102, just 100.

You would want a trigger if you absolutely must do this in the database.

I'm rather rusty on my MySQL, but maybe something like this?

CREATE TRIGGER below_one_hundred_trig AFTER UPDATE ON some_table
    FOR EACH ROW BEGIN
        UPDATE some_table SET some_value = 100 WHERE some_value > 100 AND table_pk = NEW.table_pk;
    END;

lol - but it's simple and makes everything look pretty

It's also the best way to not properly learn how to use a relational database.

In fact my solution is also a solution that does not properly use a relation database as well IMHO and I think this check should be done in software and constraints added to prevent it from happening, but this is what you asked for.

The proper usage of a trigger would be something like inserting into a history table when a table gets updated, inserted, or deleted. Once again, that's my opinion, nothing says you can't do crazy things, it just might not end well.

Honestly, you really need to tell us what you're trying to do if you want to do it right. You example is too vague to do that.

 

[W1zzard]

UPDATE foo SET bar=LEAST(bar+5, 100)

phpmyadmin is great

 

[Aquinus]

phpmyadmin is great

I think there are many people who would disagree with you and would say that PHPMyAdmin is not the proper way to manage your database. It certainly doesn't help you learn it nearly as quickly since everything is point and click, you have to remember very little and when you do need to go write some complex SQL, you don't want to be relying on PHPMyAdmin to do it.

I use a CLI/REPL whenever it comes down to working with a database directly, be it MySQL or PostgreSQL, but as I said, I don't touch MySQL very often and I never touch any PHP***Admin tools.

 

[Akumos]

Thanks, sorted! - It was interesting reading your opinions of PHPMyAdmin too