Update with join and case

[Jessica]

I am trying to do a one-time cleanup of some data that needed to be normalized. I have "rejection reasons" which have now been mapped to "rejection_code_id". I need to update a table that has no rejection_code_id for some of it's rows, and give it the right ID based on the entry in the historical_rejection table.

 

Relevant columns:

 

historical_rejection:

rejection_reason

candidate_id

 

candidate_TEMP:

candidate_id

rejection_code_id

 

rejection_code

(not used in the update)

rejection_code_id

rejection_code_value

 

I was doing them one by one, and the data got refreshed from the prod site and wiped out all my changes.

 

one-by-one query:

UPDATE candidate_TEMP c

LEFT JOIN `historical_rejection` r
ON c.candidate_id = r.candidate_id

SET c.rejection_code_id = 1019

WHERE rejection_reason = 'Sample Rejection 1'
AND (rejection_code_id = 0 OR rejection_code_id IS NULL)

 

I thought I could do it in one query using a case. I tried to do these two at once with my case, and it said 0 rows affected.

 

UPDATE candidate_TEMP c

INNER JOIN historical_rejection r

ON c.candidate_id = r.candidate_id

SET c.ats_jobseeker_rejection_code_id =

CASE

 

WHEN rejection_reason = 'Sample Rejection 1'

THEN 1028

 

WHEN rejection_reason = 'Sample Rejection 2'

THEN 1016

 

ELSE c.rejection_code_id -- I want to keep the 0/null value that exists if I don't list a matching one for it

 

END

WHERE (rejection_code_id = 0 OR rejection_code_id IS NULL)

 

I also tried using rejection_reason IN ('Sample Rejection 2') instead of just the = because I will have more than one map to the same new ID (typos in the old data), but that gave the same result.

 

What's wrong with my new update?

[Jessica]

:/ I don't have any new information to add, I tried writing them again hoping maybe I had made a mistake I can't see, and got the same result - the update with the case statement runs, no error, but makes no changes. Then the single update runs and makes the change.

 

[jazzman1]

This query works just fine, I tested few minutes ago :

UPDATE candidate_TEMP c
INNER JOIN historical_rejection r
ON c.candidate_id = r.candidate_id
SET c.ats_jobseeker_rejection_code_id =
CASE
   
   WHEN rejection_reason = 'Sample Rejection 1'
   THEN 1028

   WHEN rejection_reason = 'Sample Rejection 2'
   THEN 1016
   
   ELSE c.rejection_code_id -- I want to keep the 0/null value that exists if I don't list a matching one for it
   
END
WHERE (rejection_code_id = 0 OR rejection_code_id IS NULL)

 

RESULT:

 

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

[Jessica]

:facewall:

 

Thanks for testing it out, I guess there is some other issue I'm not seeing. I'll keep working on it.

[Christian F.]

Do you have some example data that we can play around with?