Is the following a valid update query

mainrotor
mainrotor used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
You are commenting, your update directive includes a from which makes that part invalid.

Are you trying to define two tables using CTE
Comment out the from tblA

Posting the query that seems to be giving you an error, and posing the question as you have requires to figure out what it is you are trying to do based on the query.
Distinguished Expert 2017

Commented:
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 HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
I parsed the command and didn't get any error but doesn't mean that will do what you expected it to do.
I can see that in the CTE it's refering to the tables with the server name INFORPROD, so are the tables in a different server? If so, then get sure that you have the correct linked server name configured.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial