CODEWITHSUNDEEP
sqlsql-server
John Smith Junior
John Smith Junior

Reputation: 5

Run the Update Statement from another Select in SQL query

I am trying to create an update query that will update my table values based on pallet number match.

SELECT [pallet] [quantity] 
FROM dba.Inventory

This will returns 2 columns, one with pallet and the other with count.

I need to put this in a update statement that will match each pallet between here and table TABLE1 and update the counts in TABLE1

Upvotes: 0

Views: 84

Answers (2)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

I think that one easy solution (without evaluating the SELECT itself that looks quite convoluted) is to put the whole SELECT into a CTE and use it as source for your update. Something like this:

;WITH SrcCte AS (
    SELECT pallet, SUM(total) as quantity
    FROM ( 

    SELECT r1.pallet, total 
    FROM
    (SELECT plet_nbr, COUNT(serl_nbr) as total FROM Inventory group by plet_nbr  )c1
    right join
    (select pallet from dbo.RFS where status NOT LIKE 'Not Available')r1
    on  r1.pallet = c1.plet_nbr 

    UNION all

    Select r2.pallet, sum(iloc_qty) as total
    FROM
    (SELECT plet_nbr, iloc_qty FROM Inventory WHERE([matl_nbr] Like '#%')) c2
    right join
    (select pallet from dbo.RFS where status NOT LIKE 'Not Available') r2
    on  r2.pallet = c2.plet_nbr 
    where iloc_qty is not null
    GROUP BY r2.pallet 
    )
    AS final

    GROUP BY pallet
)
UPDATE Dest
SET Dest.Cnt = Src.Quantity             
FROM Table1 AS Dest
    JOIN SrcCte AS Src ON Src.pallet = Dest.pallet

Upvotes: 0

Sravan Kumar
Sravan Kumar

Reputation: 51

Use Common Table Expression.

Syntax goes like

with CTE_Values()
AS
( --- Your Statement---)
Update T
Set Col = C.col
From Table T Join CTE_Values C
On .....

Upvotes: 1

Related Questions