August 31, 2010 at 2:42 pm
How to use multiple update with WITH SQL statement. I’m getting error message invalid object. ?
WITH a_BO(TEST_CUST_NUM, TEST_CUST_TERM_DATE, TEST_B0_NUM) as
(
SELECT a.CUST_NUM, a.CUST_TERM_DATE, b.B0_NUM
FROM PS_TEST a
INNER JOIN PS_VALUE v ON v.UDF_VALUE = a.CUST_NUM
INNER JOIN PS_PERSON p on p.PERSONNUM = v.PERSONNUM
INNER JOIN PS_SI b ON b.B0_NUM = p.B0_NUM
WHERE CUST_TERM_DATE <= CONVERT(VARCHAR(10), GETDATE(), 101)
AND CUST_TERM_DATE ’00/00/00′ a
)
UPDATE o
SET BO_CM_END_DT = x.TEST_CUST_TERM_DATE
FROM PS_CX o
JOIN a_BO AS x
on o.B0_NUM = x.TEST_B0_NUM
UPDATE r
SET ROLE_END_DT = x.TEST_CUST_TERM_DATE
FROM PS_X_ROLE r
JOIN a_BO as x
on r.B0_NUM = x.TEST_B0_NUM;
(4 row(s) affected)
Msg 208, Level 16, State 1, Line 20
Invalid object name ‘a_BO’.
August 31, 2010 at 3:10 pm
Edit: Nevermind...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2010 at 3:16 pm
The WITH clause in this scenario can be seen as a subquery, just easier to read. So it's not available for the next statement.
In order to use it for more than one statement you'd nee to insert the result of your CTE into a temp table (or table variable):
SELECT
a.CUST_NUM AS TEST_CUST_NUM,
a.CUST_TERM_DATE AS TEST_CUST_TERM_DATE,
b.B0_NUM AS TEST_B0_NUM
INTO #a_BO
FROM PS_TEST a
INNER JOIN PS_VALUE v ON v.UDF_VALUE = a.CUST_NUM
INNER JOIN PS_PERSON p on p.PERSONNUM = v.PERSONNUM
INNER JOIN PS_SI b ON b.B0_NUM = p.B0_NUM
WHERE CUST_TERM_DATE <= CONVERT(VARCHAR(10), GETDATE(), 101)
AND CUST_TERM_DATE ’00/00/00' a
UPDATE o
SET BO_CM_END_DT = x.TEST_CUST_TERM_DATE
FROM PS_CX o
JOIN #a_BO AS x
on o.B0_NUM = x.TEST_B0_NUM
UPDATE r
SET ROLE_END_DT = x.TEST_CUST_TERM_DATE
FROM PS_X_ROLE r
JOIN #a_BO as x
on r.B0_NUM = x.TEST_B0_NUM;
DROP TABLE #a_BO
September 1, 2010 at 8:00 am
Thank you. It worked.
September 1, 2010 at 10:05 am
kshatriya24 (9/1/2010)
Thank you. It worked.
Glad I could help 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply