Update statement still bugging me.

[jonki]

$query6 = "UPDATE db SET tarifa = CASE(max3 < 5 THEN \"2\", ((5 <= max3) AND (max3 < 10)) THEN \"1.8\", 
		((10 <= max3) AND (max3 < 20)) THEN \"1.5\", ((20 <= max3) AND (max3 < 50)) THEN \"1.2\", ((50 <= max3) AND (max3 < 80)) THEN \"1.1\", 
		(80 <= max3) THEN \"1\")" ;
		$result6 = mysql_query($query6);

I get no error now but the column "tarifa" is not updated even though I have several "test entries" in my db with varying values of max3

I tried it with and without the double brackets around the ((20 <= max3) AND (max3 < 50))

Any more help will be greatly appreciated

Thanks

Jon

[nemonoman]

To test, change this to a SELECT statement and check results:

SELECT CASE(max3 < 5 THEN \"2\", ((5 <= max3) AND (max3 < 10)) THEN \"1.8\",
((10 <= max3) AND (max3 < 20)) THEN \"1.5\", ((20 <= max3) AND (max3 < 50)) THEN \"1.2\", ((50 <= max3) AND (max3 < 80)) THEN \"1.1\",
(80 <= max3) THEN \"1\") from db

Adjust parens, etc, as needed, until you SELECT the results you want.

Then use the corrected CASE to UPDATE.

You might also add an ELSE case, like:
ELSE '99999'

and prove that the statement is processing through to the ELSE case.

You might also look up the BETWEEN syntax in MySQL

CASE(max3 BETWEEN 5 AND 10 THEN 1.8, max3 BETWEEN 10 AND 20 THEN 1.2 ....

etc.

[jonki]

Thanks again nemonoman - it is the commas. I removed those and it seems to be working fine.

Thanks a lot for all your help.