CODEWITHSUNDEEP
sql-servert-sqljoinsql-updatecase
Kaptah
Kaptah

Reputation: 9901

Selective UPDATE Using CASE from two tables

I have two tables Item and Measure.

Item:

ItemName    |   Unit    |   Extra5
------------|-----------|--------
bolt        |   PCS     |   
nut         |   M2      |   
Screw       |   M3      |   
Slug        |   JM      |

Measure:

ItemName    |   Unit    |   QtyPerMeasure
------------|-----------|--------------
bolt        |   PCS     |   1
bolt        |   M2      |   2
bolt        |   M3      |   3
bolt        |   JM      |   4
nut         |   PCS     |   11
nut         |   M2      |   22
nut         |   M3      |   33
nut         |   JM      |   44
Screw       |   PCS     |   111
Screw       |   M2      |   222
Screw       |   M3      |   333
Screw       |   JM      |   444
Slug        |   PCS     |   1.1
Slug        |   M2      |   2.2
Slug        |   M3      |   3.3
Slug        |   JM      |   4.4

I need to update field Extra5 in table Item in a way that query selects the corresponding value based on Unit.

Result should look like: 

ItemName    |   Unit    |   Extra5
------------|-----------|--------
bolt        |   PCS     |   1
nut         |   M2      |   22
Screw       |   M3      |   333
Slug        |   JM      |   4.4

What I've tried so far: 

UPDATE 
    Item 
SET 
    Extra5 = 
        CASE (SELECT Unit FROM Item)
        WHEN 'PCS' THEN SELECT Unit FROM Measure WHERE yksikko = 'PCS'
        WHEN 'JM' THEN SELECT Unit FROM Measure WHERE yksikko = 'JM'
        WHEN 'M2' THEN SELECT Unit FROM Measure WHERE yksikko = 'M2'
        WHEN 'M3' THEN SELECT Unit FROM Measure WHERE yksikko = 'M3'
FROM
    Item a
    INNER JOIN Measure b
        ON a.ItemName = b.ItemName

This gives me an error : Msg 156, Level 15, State 1, Line 16. Incorrect syntax near the keyword 'SELECT'.

I was thinking if CASE is after all the correct approach. Am I going in totally wrong direction?

Any help would be highly appreciated.

Upvotes: 1

Views: 49

Answers (3)

JIKEN
JIKEN

Reputation: 337

UPDATE I
SET I.Extra5 = M.QtyPerMeasure
FROM Item AS I
INNER JOIN Measure AS M ON M.ItemName = I.ItemName
    AND M.Unit = I.Unit

Explanation: Query will update Extra5 column by matching itemname and unit column values of Item and Measure tables.

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

You may try this. Here you can use inner join to get your desired result. Then simply update them as per the need. Since Item_name and Unit are same and combination of this is making unique key to update.

UPDATE ITEM
SET ITEM.Extra5 = MEASURE.QtyPerMeasure
FROM Item AS ITEM INNER JOIN Measure AS MEASURE 
ON ITEM.Unit = MEASURE.Unit AND ITEM.ItemName = MEASURE.ItemName

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

I think you just trying to do this.

UPDATE 
    Item 
SET 
    Extra5 = ISNULL(c.Unit, a.Unit)
FROM
    Item a
    INNER JOIN Measure b
        ON a.ItemName = b.ItemName
    LEFT JOIN Measure c 
        ON a.Unit = c.yksikko

Upvotes: 0

Related Questions