MySQL columns and an upper limit.?

[kreak]

I want to update a table with a few thousands rows of data in it. I want to increment one column by another column but I want the result to have a hardcoded upper limit... I can do this easily with two queries, but I want to do it in one... Something like :

" update [database].[table] set [column a]=[column a]+[column b];"
" update [database].[table] set [column a]=[upper limit] where [column a]>[upper limit];"

As a single query... If anyone can help me out there's some juicy stars waiting for you.

Thanks in advance.
dhvrm :

Perfect, I'm usually quite good at finding things like this out myself but the MySQL manual and my brain just don't mesh well together...

I'd 5 star you now, but I've got to wait a bit.

Thanks!
Oooo.. Actually after testing out the initial answer, it appears the second may be the more accurate one.

Let's see when the timer expires, thanks for the information!
Well, so far neither of these actually get the result I expected... Which is weird... I'm not actually doing a column_a+column_b at the moment, but I will do in the future, but I presumed it would be the same for a value..?

Here's the actual Python code I am executing.


self.cursor.execute('update '+DB_NAME+'.acc
SET stamina=CASE
WHEN col_a+'+str(float(self.parent.CFG[0]["col_a_increase"]))+' > '+str(100)+' THEN 100
ELSE col_a=col_a+'+str(float(self.parent.CFG[0]["col_a_increase"]))+'
END;')

I'm sure that'll look horrible on here, but it's a basic representation of the code... Where I've got str(100) I know I could just hardcode 100 in to the query, but later on that's going to be flexible...

Anyway, I think I might be able to sort this out based on the info you both gave, thanks again! If you spot something I've done wrong then I appreciate the additional, additional assistance

*Grin*

[martin]

UPDATE table
SET columnA = SELECT CASE
WHEN columnA + columnB > limit THEN limit
ELSE columnA + columnB
END

UPDATE:

Yes, you need to take the SELECT out of my original code.

UPDATE table
SET columnA = CASE
WHEN columnA + columnB > limit THEN limit
ELSE columnA + columnB
END

[esedge]

Hey there.

if you have MySQL 5.0 this query should do.

You can control the flow of your query using Control Flow Functoins:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
In this case the Case statement in Mysql is almost like the SQL Server one.

UPDATE [database].[table] SET [column a]=
CASE WHEN [column a]+[column b] > [upper limit] THEN [column a]+[column b]
ELSE [upper limit] END

Hope it helps.