Hello,

I'm getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF cat_order = '3' THEN SET cat_order = '2' [ELSEI' at line 3

From this query:

$updateOrder = "
 UPDATE
  products_cat
 IF
  cat_order = '".mysql_real_escape_string($neworder)."'
   THEN 
    SET
     cat_order = '".mysql_real_escape_string($row_rs_sortvalues['cat_order'])."'
 [ELSEIF
  cat_order = '".mysql_real_escape_string($row_rs_sortvalues['cat_order'])."'
   THEN
    SET
     cat_order = '".mysql_real_escape_string($neworder)."'
 ]
 END IF
 WHERE
  cat_group_order = '".mysql_real_escape_string($row_rs_sortvalues['cat_group_order'])."'
  AND cat_level = '".mysql_real_escape_string($row_rs_sortvalues['cat_level'])."'
";
mysql_select_db($database_conn, $conn);
$ResultOrder = mysql_query($updateOrder, $conn) or die(mysql_error());

When I echo the completed query this is what it looks like:

UPDATE
 products_cat
IF
 cat_order = '3'
 THEN
  SET
   cat_order = '2'
[ELSEIF
 cat_order = '2'
  THEN
   SET
    cat_order = '3'
 ]
END IF
WHERE
 cat_group_order = '2'
 AND cat_level = '2'

Can anyone spot an error in that? This is my first time using an IF statement, am I using it correctly?

Thanks for any and all help!

Peter

    After some further searching, I think the IF statement can only be used with stored procedures and functions. Is this true? If so, is there a simple way to duplicate what I am trying to do above...?

      I believe your analysis is correct. Within a normal SQL statement you need to use the IF() function.

        Thanks NogDog,

        I've read up on the IF() function but can't find an example similar to what I'm trying to do. Do you know how I could use IF() to Update a column with one of several values depending on it's own content?

        In simple terms, something similar to:

        UPDATE
 products_cat
IF
cat_order = '3' SET cat_order = '2'
ELSEIF
cat_order = '2' SET cat_order = '3'
WHERE
cat_level = '2'

        ... essentially, replace any 3s with 2s and any 2s with 3s.

        Thanks again!

          I think you would want something like:

          UPDATE products_cat 
set cat_order = IF(cat_order = '3', '2', IF(cat_order = '2', '3', cat_order))
WHERE cat_level = '2'

            Thanks my friend, worked perfectly!!! So simple, why they don't list that as an example in the MySQL manual is beyond me.

            Finally I can move on! A thousand thank-yous......

              Write a Reply...