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.PICKDET
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
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.PICKDET
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
........
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