0

Job_ID column in Employees table has unique data for each row and i need to update those data either as 'MANAGER" OR 'TELLER' using value of another column FIRST_NAME as below but seems the update is not happening. My expectation is for FIRST_NAME = VIJES OR UNNATH the JOB_ID should be "Manager" and for others should be "Teller"

Getting response as 0 row(s) affected Rows matched: 6 Changed: 0 Warnings: 0

UPDATE EMPLOYEES
SET JOB_ID = CASE
WHEN FIRST_NAME IN ('VIJES','UNNATH') THEN JOB_ID = 'MANAGER'

WHEN FIRST_NAME NOT IN ('VIJES','UNNATH') THEN JOB._ID = 'TELLER'
END
WHERE FIRST_NAME IN ('VIJES','UNNATH','VINOD','RAKESH','ANANT','MUKESH')

Understand that i can fire individual Update Query with criteria FIRST_NAME for each row but looking for single Update Query for entire table.

Please clarify

badri
  • 31
  • 8

1 Answers1

0

The syntax you should be using is assigning the JOB_ID column to a CASE expression, and nothing more. Currently, you are assigning to a CASE expression which is then again trying to assign a value to the column being updated.

Try using this query:

UPDATE EMPLOYEES
SET JOB_ID = CASE WHEN FIRST_NAME IN ('VIJES','UNNATH')
                  THEN 'MANAGER'
                  ELSE 'TELLER' END
WHERE FIRST_NAME IN ('VIJES', 'UNNATH', 'VINOD', 'RAKESH', 'ANANT', 'MUKESH')
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Please find any one of several duplicates to close this page with. (here's a headstart: https://stackoverflow.com/questions/25340135/mysql-update-multiple-values-and-where-in , https://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query , https://stackoverflow.com/questions/9346755/update-multiple-rows-in-1-column-in-mysql) As a veteran and SME, we need you to proactively help us combat rampant, redundant site bloat. Please help us. – mickmackusa Jan 31 '21 at 00:14