Avatar of mainrotor
mainrotor
 asked on

Is the following a valid update query

Hi Experts,
Is the following a valid UPDATE query in SQL Server 2008.


With tblA as
(
SELECT ORS.EXTERNORDERKEY, PDTL.SKU, SUM(PDTL.QTY) AS ITEMQTY
FROM INFORPROD.PRD1.WH1.CASEID CID
INNER JOIN INFORPROD.PRD1.WH1.ORDERS ORS ON CID.REFERENCE1 =  ORS.ORDERKEY
INNER JOIN INFORPROD.PRD1.WH1.PICKDETAIL PDTL ON PDTL.CASEID = CID.CASEID
INNER JOIN INFORPROD.PRD1.WH1.SKU SKU ON SKU.SKU = PDTL.SKU AND SKU.STORERKEY = PDTL.STORERKEY
WHERE ORS.EXTERNORDERKEY = 'WEB229046'
GROUP BY ORS.EXTERNORDERKEY, PDTL.SKU
)
,
tblB as
(
SELECT *
FROM SOP10200_BAK09092015
WHERE SOPNUMBE = 'WEB229046'
)
--SELECT * FROM tblA
UPDATE tblB --SOP10200_BAK09092015
SET QTYFULFI = X.ITEMQTY,
QTYSLCTD = X.ITEMQTY,
FUFILDAT = GETDATE()
From tblA X
WHERE tblB.SOPNUMBE = X.EXTERNORDERKEY
AND tblB.ITEMNMBR = X.SKU
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
arnold

If you need to join tblB and tbla within the update query.


........


What are you after,  your update seems to point to columns that do not exist in tblb

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
Jim Horn

You tell us.  You're connected to the data source, and we're not, so all experts here can do is eyeball your query and tell if the syntax is correct.  

We can't run it, especially run it within a transaction that is rolled back with a 'SELECT @@ROWCOUNT' line to see whether it ran without error and how many rows were affected without making the update permanent. (See SQL Server Delete Duplicate Rows Solutions, section 'Extra Stuff on DELETE #1 How to test your DELETE statement using a transaction' for a demo.)

Looks correct based on what we've been provided, and assuming columns QTYFULFI, QTYSLCTD, and FUFILDAT are all in tblB / SOP10200_BAK09092015, although you'll need a semi-colon before the first WITH statement.

btw the use of alias X in the main query is kinda pointless, better to just stick with tblA
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes