Updates and Self Joins and Case Statements - oh my

Question

I think I may be trying to do too much with one query, and it's been driving me batty.

I have two tables, Source and Zip_Code. The Source table has a zip code field that can either be 3 or 5 digits.

If it's 3 digits, I need to join to the zip_code table's 3-digit field and set the Source's new_zip field to the zip_code table's corresponding 5-digit entry.

If it's 5 digits, I need to link to the zip_code table's 5-digit field (to make sure it's a valid zip code), then put that 5-digit number in the Source's new_zip field.

I already understand that you can't manipulate something you're updating, hence using a self-join to manipulate a copy. So now I need to create Source2, and join Source1, Source2, and Zip_Code tables.

UPDATE SOURCE1
INNER JOIN SOURCE2 ON SOURCE1_UID = SOURCE2_UID
INNER JOIN ZIP_CODE ON SOURCE1_ZIP =
   (   
    SELECT
      (
       CASE WHEN LENGTH(SOURCE1.ZIP <4
       THEN ZIP_CODE.3_DIGIT
       ELSE ZIP_CODE.5_DIGIT
       END
       )
    FROM SOURCE2
    ) 

SET SOURCE1.NEW_ZIP = 
 (
  CASE WHEN LENGTH(SOURCE1.ZIP <4
  THEN ZIP_CODE.3_DIGIT
  ELSE ZIP_CODE.5_DIGIT
  END
  )

I know I'm doing something majorly wrong, but I'm having a block as to where.

score 1 · Accepted Answer · answered Sep 27 '18 at 17:03

You're not naming the Source table in the query. You need to name the table and then give it an alias like Source1 if you have to refer to it twice.

But I don't think there's any need for the self-join.

There's no need for the subqueries, you can use conditionals in an ON clause.

UPDATE Source AS s
JOIN Zip_Code AS z ON 
    CASE WHEN LENGTH(s.zip) = 3
        THEN z.3_digit
        ELSE z.5_digit
    END
SET s.new_zip = z.5_digit

Your description says that you always want to set new_zip to the 5-digit code, so there's no need for a CASE in the SET clause.

Updates and Self Joins and Case Statements - oh my

1 Answers1