Update one table based on data from another table with different data types-CodePudding

for example, I got table order looks like

| orderId | type |
| --------|------|
| 1       |   pig|
| 2       |   cat|
| 3       |   cat|

and here I got a table adopt

|orderId|type|
| --- |---|
|1    |  1|
|2    |  2| 
|3    |  0|

I want to update the type value(int) of table adopt depends on the type value(string) of table order, pig means 1 ,cat means 2, so the third row in table adopt should look like (3,2), is there any sql can make this work, or I should use programming language instead.

CodePudding user response：

update adopt a
join `order` o on o.orderId = a.orderId
set a.`type` = case when o.`type` = 'pig' then 1
                    when o.`type` = 'cat' then 2
                    else 0 
               end