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
mainrotorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.