Unrecognized keyword. in PHP

Question

hi my name is milind and trying to mysql UPDATE query fire with CASE and WHEN statement and i getting this error below:

24 errors were found during analysis.

Unrecognized keyword. (near "CASE" at position 27)
Unrecognized keyword. (near "WHEN" at position 32)
Unexpected token. (near "id" at position 37)
Unexpected token. (near "=" at position 39)
Unexpected token. (near "'2'" at position 40)
Unrecognized keyword. (near "THEN" at position 44)
Unexpected token. (near "'Y'" at position 49)
Unrecognized keyword. (near "ELSE" at position 53)
Unrecognized keyword. (near "status" at position 58)
Unrecognized keyword. (near "END" at position 65)
Unexpected token. (near "," at position 68)
Unrecognized keyword. (near "status" at position 71)
Unexpected token. (near "!=" at position 78)
Unrecognized keyword. (near "CASE" at position 81)
Unrecognized keyword. (near "WHEN" at position 86)
Unrecognized keyword. (near "status" at position 91)
Unexpected token. (near "=" at position 98)
Unexpected token. (near "'2'" at position 99)
Unrecognized keyword. (near "THEN" at position 103)
Unexpected token. (near "'N'" at position 108)
Unrecognized keyword. (near "ELSE" at position 112)
Unrecognized keyword. (near "status" at position 117)
Unrecognized keyword. (near "END" at position 124)
Unexpected token. (near "," at position 127)

and my UPDETE query is below:

UPDATE quotes SET status = CASE WHEN id='2' THEN 'Y' ELSE status END,
status = CASE WHEN id !='2' THEN 'N' ELSE status END,
WHERE id='2';

i want update where status=Y insted of status=N and where stutus=N insted of status=Y.

`!=` is not SQL syntax. When compaing strings you could e.g. write `id not like '2' then` — PhillipD, Dec 07 '17 at 07:37
@PhillipD, `!=` is not ANSI SQL standard syntax, but it is recognized in almost every SQL product. See my answer to https://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-t-sql/723426#723426 — Bill Karwin, Dec 07 '17 at 07:49
@BillKarwin thank you for the information and the linked answer. — PhillipD, Dec 07 '17 at 09:28

score 2 · Answer 1 · answered Dec 07 '17 at 07:37

You seem to have misunderstood how to use the CASE statement. See the example below from this MySQL tutorial

DELIMITER $$

CREATE PROCEDURE GetCustomerShipping(
 in  p_customerNumber int(11), 
 out p_shiping        varchar(50))
BEGIN
    DECLARE customerCountry varchar(50);

    SELECT country INTO customerCountry
 FROM customers
 WHERE customerNumber = p_customerNumber;

    CASE customerCountry
 WHEN  'USA' THEN
    SET p_shiping = '2-day Shipping';
 WHEN 'Canada' THEN
    SET p_shiping = '3-day Shipping';
 ELSE
    SET p_shiping = '5-day Shipping';
 END CASE;

END$$

Unrecognized keyword. in PHP

1 Answers1