Update multiple columns Table from a select statement

Question

I am trying to update a table from a select statement but none of my rows are being affected. If I run the select statement I get results so I am not sure . In the example I narrowed down the where clause. Any help will be appreciated.

My Select

select 
    a.Code,
    a.ItemGroup,
    a.Pack,
    a.bar_code,
    WHStockLink,WHUseInfoDefs,
    b.[iBinLocationID],
    c.cBinLocationDescription,
    ucIIWBin1,
    WHPackCode,
    WHBarCode,
    IdWhseStk
from
    WhseStk b 
    join StkItem a on a.StockLink = WHStockLink
    inner join _btblBINLocation c on c.idBinLocation = b.iBinLocationID 
where 
    WHWhseID = 3 and 
    IdWhseStk = 1003000221089 
order by WHStockLink

My Update

update WhseStk
    set ucIIWBin1 = t1.cBinLocationDescription
    from (
        select
            a.Code,
            a.ItemGroup,
            a.Pack,
            a.bar_code,
            WHStockLink,
            WHUseInfoDefs,
            b.[iBinLocationID],
            c.cBinLocationDescription,
            ucIIWBin1,
            WHPackCode,
            WHBarCode,
            IdWhseStk
        from  
            WhseStk b 
            join StkItem a on a.StockLink = WHStockLink
            join _btblBINLocation c on c.idBinLocation = b.iBinLocationID 
        --where WHWhseID = 5 and c.idBinLocation = b.iBinLocationID
         ) t1
    where WHWhseID = 5

If I tweak it every row gets affected with the same value .

What I am trying to to is actually to match using the stock link and Whse and populate 3 new user defined fields in the same table ie ucIIWBin1, ucIIWBin2, ucIIWBin13, which are the Bin Description (joined from another table) the barcode and pack from this table.

Whse:

Bin:

Stock:

GMB · Accepted Answer · 2018-12-11 12:21:50Z

1

As you tagged your question tsql, I assume that you are using sql-server.

In this case, you can have JOINs in your UPDATE statement. Based on the query you displayed, the syntax would look like :

UPDATE a
SET a.ucIIWBin1 = c.cBinLocationDescription
FROM WhseStk a
INNER JOIN StkItem b ON b.StockLink = a.WHStockLink
INNER JOIN _btblBINLocation c on c.idBinLocation = a.iBinLocationID 
WHERE a.WHWhseID = 5

As you mentionned that your queries were narrowed down, you might need to adapt the above example (especially, you mentionned that you need to update 3 fields while you show only one in your code).

edited Dec 11, 2018 at 12:21

answered Dec 10, 2018 at 15:35

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hassan Over a year ago

Thank you I havnt tried this way before , to update the 3 fields would it be best to run 3 separate statements or add to this one , there is a fair amount of records ?

GMB Over a year ago

Add to this one ; do not duplicate statements, it will be much harder to maintain, and performance will suffer

Collectives™ on Stack Overflow

Update multiple columns Table from a select statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related