Compare 2 table columns and update new column

meysam motamedi Published at Dev

meysam motamedi

I have 2 tables like this:

enter image description here

For example in first row from table 2 col value is 3661 and its equal to 'med' from table 1 I want to update class from table 1 with 'MED' and ...

This is the code I wrote but something is wrong :(

UPDATE table1 SET
class = ( CASE 
            WHEN table2.col = table1.med
            THEN  'MED'
            --------------------------------
            WHEN table2.col = table1.mgl
            THEN  'MGL'
            --------------------------------
            WHEN table2.col = table1.rhp
            THEN  'RHP'
            --------------------------------
            WHEN table2.col = table1.epd
            THEN  'EPD'
            --------------------------------
            WHEN table2.col = table1.jpa
            THEN  'JPA'
            --------------------------------
            ELSE  'NULL'
          FROM 
            table1 LEFT outer JOIN table2
            )

Ormoz

I edited your code. you did not mention which database you use, Anyway try this:

   UPDATE table1 
    SET class = ( CASE 
                WHEN table2.col = table1.med
                THEN  'MED'
                --------------------------------
                WHEN table2.col = table1.mgl
                THEN  'MGL'
                --------------------------------
                WHEN table2.col = table1.rhp
                THEN  'RHP'
                --------------------------------
                WHEN table2.col = table1.epd
                THEN  'EPD'
                --------------------------------
                WHEN table2.col = table1.jpa
                THEN  'JPA'
                --------------------------------
                ELSE  'NULL'
    END)
    from table1 left join table2 on table1.number=table2.number

Collected from the Internet

Please contact [email protected] to delete if infringement.