Urgent CASE question

[jonki]

Hi - I have a problem in this code and it worked fine until I put in the AND (flex='si').
The column flex can be 'si' or 'no' - but this query updates the entire database regardles of what is in the flex column.

Any ideas?

This job runs as a cron job every Sunday night and it messed up my entire database - if anyone can give me an idea before then I would be sooooo grateful. Surely I am simply doing something wrong with apostrophies or = instead of == or something.

$result = mysql_query($query); //run the query 

// I need to update the tarifa here based on max for week 1
$query6 = "UPDATE tenercoche_concesionarios SET tarifa = CASE 
WHEN ((max2 < 5) AND (flex='si')) THEN \"2\" 
WHEN((5 <= max2) AND (max2 < 10) AND (flex='si')) THEN \"1.8\"
ELSE \"0\" 
END " ;
$result6 = mysql_query($query6) or die (mysql_error() );

[tsinka]

Hi,

what should the query do exactly ?

Thomas

[jonki]

I want the "tarifa" to be altered depending on the size of "max2".
But I only want this to affect the columns that are "flexible"

So that when max2 < 5 tarifa is set to 2
when max2 is between 5 and 10 then tarifa is set to 1.8

it goes on but I have only included these two lines for simplicity.

Thanks a lot.

[tsinka]

Ok,

try something like this:

UPDATE tenercoche_concesionarios 
SET tarifa = CASE WHEN max2<5 THEN '2'
WHEN max2>='5' AND max2<'10' THEN '1.8' 
ELSE '0' END WHERE flex='si'

Thomas

[jonki]

We'll see tomorrow if it works.

I appreciate your help.

Jon