Re: SQL conditional UPDATE

Thanks, I actually gave up on it yesterday and wrote out the queries individually (8 of them) as a workaround. (It was on Access2000 not MySQL btw). Today, I was looking at a default value formula for Access and used the function IIF (think I've used it before ages ago and then forgot all about it and of course didn't find it when I was searching for "IF") which I got to work and then thought it was worth a shot trying it in SQL. Success! The syntax is "UPDATE table SET field1 = (IIF (condition,valuea,valueb)) WHERE someothercondition;" Happy day. Now I'm stuck trying to use fields in the default rule - IIF works in default rules eg IIF(1<2,"egg","chicken") but as soon as you include a field eg IIF([whichfirst]>1,"egg","chicken") it spits out... "The database engine does not recognize either the field <name> in a validation expression, or the default value in the table <name>. (Error 3344) Your expression refers to a field incorrectly, or to a field that does not exist. Check to make sure the field name is valid and spelled correctly." According to the help screens I've looked at you should be able to reference fields, I've tried [field], "[field]", [table].[field], [table]![field] all to no avail. It is definitely spelled correctly btw. Derek {giving consideration to the aerodynamic properties of a PC} ----- Original Message ----- From: <php3@developersdesk.com> To: "Derek Paterson" <d.paterson@craneware.com>; <php-db@lists.php.net> Sent: Thursday, October 12, 2000 5:03 PM Subject: Re: [PHP-DB] SQL conditional UPDATE Addressed to: "Derek Paterson" <d.paterson@craneware.com> php-db@lists.php.net ** Reply to note from "Derek Paterson" <d.paterson@craneware.com> Thu, 12 Oct 2000 14:46:51 +0100 > > Is there any way within one SQL query to Update to different values > dependant on conditions. I've guessed at subqueries but cannot get my > head round the syntax and web advice differs dependant on source. eg. > UPDATE table SET field1 = "valuea" WHERE condition1 ELSE field1 = > "valueb" > UPDATE table SET field1 = IF( condition1, 'valuea', 'valueb' ); An hour or two spend perusing the MySQL documentation might be very valuable. Chapter 7, the MySQL Language Reference is the area to spend most of your time. http://www.mysql.com/documentation/mysql/commented/manual.php?section=Refere nce Within that section you will find 7.4 Functions for Use in SELECT and WHERE Clauses very useful: http://www.mysql.com/documentation/mysql/commented/manual.php?section=Functi ons Rick Widmer Internet Marketing Specialists www.developersdesk.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net