asked on

Correct syntax to update with mixture due to joins

I am trying to convert this SELECT statement into an UPDATE statement. The premise of this statement is that it looks at lines on a purchase order and gets the quantity ordered(QTYORDER) and then looks at all the associated lines on sales orders that make up that quantity(QTYONPO).

SELECT * FROM
(SELECT PONUMBER,ORD,QTYORDER,QTYCMTBASE,QTYUNCMTBASE
FROM POWMT..POP10110) T
INNER JOIN
(SELECT T1.PONUMBER,T1.ORD,SUM(T2.QTYTBAOR) AS QTYTBAOR,SUM(T2.QTYONPO) AS QTYONPO
FROM POWMT..SOP60100 T1
INNER JOIN POWMT..SOP10200 T2 ON T1.SOPNUMBE=T2.SOPNUMBE AND T1.SOPTYPE=T2.SOPTYPE AND T1.LNITMSEQ=T2.LNITMSEQ
INNER JOIN POWMT..POP10110 T3 ON T1.PONUMBER=T3.PONUMBER AND T1.ORD=T3.ORD
GROUP BY T1.PONUMBER,T1.ORD) T2
on T.PONUMBER=t2.PONUMBER and T.ORD=t2.ORD
WHERE T.QTYCMTBASE<>T2.QTYONPO
ORDER BY T.PONUMBER,T.ORD

What I am trying to do is update the QTYCMTBASE on the PO line to the corresponding QTYONPO from all the associated lines on sales orders. I am also looking to set the uncommitted quantity column(QTYUNCMTBASE) to the total quantity on the PO(QTYORDER) - committed quantity(QTYONPO).

UPDATE POWMT..POP10110
SET QTYCMTBASE=T2.QTYONPO,QTYUNCMTBASE=T.QTYORDER-QTYONPO
FROM
(SELECT PONUMBER,ORD,QTYORDER,QTYCMTBASE,QTYUNCMTBASE
FROM POWMT..POP10110) T
INNER JOIN
(SELECT T1.PONUMBER,T1.ORD,SUM(T2.QTYTBAOR) AS QTYTBAOR,SUM(T2.QTYONPO) AS QTYONPO
FROM POWMT..SOP60100 T1
INNER JOIN POWMT..SOP10200 T2 ON T1.SOPNUMBE=T2.SOPNUMBE AND T1.SOPTYPE=T2.SOPTYPE AND T1.LNITMSEQ=T2.LNITMSEQ
INNER JOIN POWMT..POP10110 T3 ON T1.PONUMBER=T3.PONUMBER AND T1.ORD=T3.ORD
GROUP BY T1.PONUMBER,T1.ORD) T2
on T.PONUMBER=t2.PONUMBER and T.ORD=t2.ORD
WHERE T.QTYCMTBASE<>T2.QTYONPO

When I run the update statement it sets values that make no sense. What is the correct way to structure this so the update will be correct. I have attached the dataset.
POSampleDataSet.xlsx

David Todd

Hi,

I created two temp tables to hold the main data from your query, reformatted it so I could read it, and then created the update query based on your sample. I've attached the full source code for my solution as the amount of data is a little large to post in the page.

HTH
David

use EE
go

if object_id( N'tempdb..#PO', N'U' ) is not null 
	drop table #PO;
	
create table #PO(
	PONumber int
	, Ord int
	, QtyOrder int
	, QtyCMTBase int
	, QtyUNCMTBase int
	)
	
if object_id( N'tempdb..#Derived', N'U' ) is not null 
	drop table #Derived;
	
create table #Derived(
	PONumber int
	, Ord int
	, QtyBAOR int
	, QtyOnPO int
	)

insert #PO
select 20172144,147456,2,23,0 union all
select 20172144,163840,2,23,0 union all
select 20181009,16384,2,68,0 union all
select 20181009,32768,2,50,50 union all
select 20181199,32768,10,50,50 --union all
-- ... etc

insert #Derived
select 20172144,147456,1,1 union all
select 20172144,163840,1,1 union all
select 20181009,16384,2,2 union all
select 20181009,32768,0,0 union all
select 20181199,32768,10,10 --union all
-- ... etc

-- your select query
select *
from #PO p
inner join #Derived d
	on p.PONumber = d.PONumber
	and p.ORD = d.ORD
where	
	p.QtyCMTBase != d.QtyOnPO
order by
	p.PONumber
	, p.ORD
;

-- update query
update p
	set QTYCMTBASE=d.QTYONPO
	, QTYUNCMTBASE=p.QTYORDER-d.QTYONPO	
from #PO p
inner join #Derived d
	on p.PONumber = d.PONumber
	and p.ORD = d.ORD
where	
	p.QtyCMTBase != d.QtyOnPO
;

Open in new window

2018-11-06-EE-a.sql

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

Thank you both. The CTE worked better but both got me going in the right direction.