CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
Hassan
Hassan

Reputation: 5

Update multiple columns Table from a select statement

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:

Upvotes: 0

Views: 4413

Answers (1)

GMB
GMB

Reputation: 222702

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).

Upvotes: 1

Related Questions