June 22, 2015 at 9:00 am
I get the error:
(0 row(s) affected)
Msg 208, Level 16, State 1, Line 41
Invalid object name 'X_SET_PREOP'.
FOR THE FOLLOWING CODE SEGMENT.. I am trying to do 2 updates with just one WITH BLOCk. Is that a problem ?
How do I get around this ?
Create table #temp( MPOG_CASE_ID uniqueidentifier, lab_name varchar(100), lab_date datetime, lab_value decimal(19,2) );
with X_SET_PREOP as
(
SELECT
xx= ROW_NUMBER() OVER ( PARTITION BY lab.MPOG_Case_ID, lab.lab_name ORDER BY lab.lab_date DESC ),
lab.MPOG_Case_ID,
lab.lab_name,
lab.lab_value,lab.lab_date
FROM
MPOG_Research..ACRC_427_lab_data lab
WHERE
LAB_STAGE = 'PREOP'
)
UPDATEv SET
v.lab_value= a.lab_value,
v.lab_date= a.lab_date
FROM #temp v
inner join
(
SELECT
MPOG_Case_ID,
lab_value,lab_date
FROM X_SET_PREOP
WHERE
XX=1
and
lab_name = 'INR'
)a
ON a.MPOG_Case_ID = v.MPOG_Case_ID
UPDATEv SET
v.lab_value= a.lab_value,
v.lab_date= a.lab_date
FROM #temp v
inner join
(
SELECT
MPOG_Case_ID,
lab_value,lab_date
FROM X_SET_PREOP
WHERE
XX=1
and
lab_name = 'HGB'
)a
ON a.MPOG_Case_ID = v.MPOG_Case_ID;
June 22, 2015 at 9:12 am
A CTE isn't a temp table, it's visible to the query (singular) that immediately follows it, that's all. Not the rest of the batch.
-- update 1
WITH X_SET_PREOP
AS (SELECT xx = ROW_NUMBER() OVER (PARTITION BY lab.MPOG_Case_ID, lab.lab_name ORDER BY lab.lab_date DESC),
lab.MPOG_Case_ID,
lab.lab_name,
lab.lab_value,
lab.lab_date
FROM MPOG_Research..ACRC_427_lab_data lab
WHERE LAB_STAGE = 'PREOP'
)
UPDATE v
SET v.lab_value = a.lab_value,
v.lab_date = a.lab_date
FROM #temp v
INNER JOIN (SELECT MPOG_Case_ID,
lab_value,
lab_date
FROM X_SET_PREOP
WHERE xx = 1 AND lab_name = 'INR'
) a ON a.MPOG_Case_ID = v.MPOG_Case_ID;
-- update 2
WITH X_SET_PREOP
AS (SELECT xx = ROW_NUMBER() OVER (PARTITION BY lab.MPOG_Case_ID, lab.lab_name ORDER BY lab.lab_date DESC),
lab.MPOG_Case_ID,
lab.lab_name,
lab.lab_value,
lab.lab_date
FROM MPOG_Research..ACRC_427_lab_data lab
WHERE LAB_STAGE = 'PREOP'
)
UPDATE v
SET v.lab_value = a.lab_value,
v.lab_date = a.lab_date
FROM #temp v
INNER JOIN (SELECT MPOG_Case_ID,
lab_value,
lab_date
FROM X_SET_PREOP
WHERE xx = 1 AND lab_name = 'HGB'
) a ON a.MPOG_Case_ID = v.MPOG_Case_ID;
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
June 22, 2015 at 9:17 am
A CTE's scope is limited to the statement immediately following its definition, which is why the second UPDATE is throwing that error.
There are a few things you could do instead.
1) Instead of using a CTE, insert the results of the query defining the CTE into a temp table and join to that temp table.
2) Just repeat the CTE definition for the second UPDATE (what Gail did).
3) Use a single UPDATE. Without knowing your data I can't really tell if there's a problem with this, but just from the code it looks like you might be able to do one UPDATE, and have the subquery get the rows for both labs, instead of one lab at a time.
Hopefully that helps clear it up!
June 22, 2015 at 9:23 am
From MSDN:
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
https://msdn.microsoft.com/en-CA/library/ms175972.aspx
June 22, 2015 at 9:27 am
Thank you
THat worked!
GOOD JOB
🙂
June 23, 2015 at 7:32 am
Jacob Wilkins (6/22/2015)
A CTE's scope is limited to the statement immediately following its definition, which is why the second UPDATE is throwing that error.There are a few things you could do instead.
1) Instead of using a CTE, insert the results of the query defining the CTE into a temp table and join to that temp table.
2) Just repeat the CTE definition for the second UPDATE (what Gail did).
3) Use a single UPDATE. Without knowing your data I can't really tell if there's a problem with this, but just from the code it looks like you might be able to do one UPDATE, and have the subquery get the rows for both labs, instead of one lab at a time.
Hopefully that helps clear it up!
Here's what the single update could look like:
WITH X_SET_PREOP AS (
SELECT xx = ROW_NUMBER() OVER (PARTITION BY lab.MPOG_Case_ID, lab.lab_name ORDER BY lab.lab_date DESC),
lab.MPOG_Case_ID,
lab.lab_name,
lab.lab_value,
lab.lab_date
FROM MPOG_Research..ACRC_427_lab_data AS lab
WHERE LAB_STAGE = 'PREOP'
AND lab.lab_name IN ('INR', 'HGB')
)
UPDATE v
SET v.lab_value = a.lab_value,
v.lab_date = a.lab_date
FROM #temp AS v
INNER JOIN X_SET_PREOP AS a
ON v.MPOG_Case_ID = a.MPOG_Case_ID
AND a.xx = 1;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 23, 2015 at 8:02 am
Folks,
This is resolved. No further assistance needed.
Thanks
June 23, 2015 at 8:33 am
mw112009 (6/23/2015)
Folks,This is resolved. No further assistance needed.
Thanks
Glad to hear you have the problem resolved. Forum etiquette would have you share the resolution so that others may benefit.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply