CODEWITHSUNDEEP
sql
meysam motamedi
meysam motamedi

Reputation: 75

Compare 2 table columns and update new column

I have 2 tables like this:

enter image description here

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
            )

Upvotes: 1

Views: 129

Answers (3)

radar
radar

Reputation: 13425

Tried it in MySQL, this is working

UPDATE Table1 
INNER JOIN Table1 T1
LEFT JOIN Table2 T2
ON T1.number = T2.number
set T1.class = 
( CASE 
  WHEN T2.col = T1.med
  THEN  'MED'
  WHEN T2.col = T1.mgl
  THEN  'MGL'
  WHEN T2.col = T1.rhp
  THEN  'RHP'
  WHEN T2.col = T1.epd
  THEN  'EPD'
  WHEN T2.col = T1.jpa
  THEN  'JPA'
  ELSE  'NULL' 
  END
  )

EDIT: In case of MS SQL SERVER, you can use this

UPDATE Table1 
set class = 
( CASE 
  WHEN T2.col = T1.med
  THEN  'MED'
  WHEN T2.col = T1.mgl
  THEN  'MGL'
  WHEN T2.col = T1.rhp
  THEN  'RHP'
  WHEN T2.col = T1.epd
  THEN  'EPD'
  WHEN T2.col = T1.jpa
  THEN  'JPA'
  ELSE  'NULL' 
  END
  )
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.number = T2.number

Upvotes: 0

Ormoz
Ormoz

Reputation: 3013

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You want something like this:

UPDATE table1
    SET class = COALESCE((SELECT MIN(CASE WHEN table2.col = table1.med THEN 'MED'
                                          WHEN table2.col = table1.mgl THEN 'MGL'
                                          . . .
                                     END) as newval
                          FROM table2
                         ), 'NULL')

This is a bit tricky. You need to decide which row you want if there are multiple matches. The above chooses an arbitrary value among the matches.

The coalesce() is to handle the case where there are no matches. The subquery will return NULL in that case.

This is standard SQL and should work in any database. Specific databases might have other ways of writing this query.

Upvotes: 1

Related Questions