Update Case Where

jonki

Hi - I have just changed some code but I am frightened to run it as if it doesn't do what I want it to do then it will mess up my whole database.

This UPDATE statement worked fine but I want to limit it to update only certain rows in the database, namely the rows where flex = 'si'.

Will this do that?

$query6 = "UPDATE tenercoche_concesionarios SET tarifa = CASE 
	WHEN (max3 < 5) THEN \"2\" 
	WHEN((5 <= max3) AND (max3 < 10)) THEN \"1.8\"
	WHEN ((10 <= max3) AND (max3 < 20)) THEN \"1.5\" 
	WHEN ((20 <= max3) AND (max3 < 50)) THEN \"1.2\" 
	WHEN ((50 <= max3) AND (max3 < 80)) THEN \"1.1\" 
	WHEN (80 <= max3) THEN \"1\"
	ELSE \"0\" 
	END;
	WHERE flex='si' " ; //This is the line I just added.
	$result6 = mysql_query($query6);

Thanks a lot, once again, fall all your amazing help.

Shrike

Sorry if I'm being a little noobish but I've never heard of WHEN and THEN statements in MySQL. Also the manual has no mention of them. WHEN and THEN are PostgreSQL statements, are you sure your using the right database? 🙂

As far as the WHERE clause, regardless of database that should limit the query, but I can't be certain.

Also, back up your database before doing large manipulations, you can just restore if it all goes wrong 😉

jonki

To be honest the only bit of code I changed was that one line - the WHEN and THEN code is working fine and was supplied to me by someone in this forum. It all works well but for the whole database.

Thanks a lot for your help.

Shrike

Ah it's part of the stored procedure syntax? Not ever used version 4 🙂

jonki

I have worked it out - I wasn't thinking properly;

This should work;

$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\"
			WHEN ((10 <= max2) AND (max2 < 20) AND (flex='si')) THEN \"1.5\" 
			WHEN ((20 <= max2) AND (max2 < 50) AND (flex='si')) THEN \"1.2\" 
			WHEN ((50 <= max2) AND (max2 < 80) AND (flex='si')) THEN \"1.1\" 
			WHEN ((80 <= max2)  AND (flex='si')) THEN \"1\"
			ELSE \"0\" 
			END; " ;
			$result6 = mysql_query($query6) or die (mysql_error() );