May 8, 2015 at 4:12 am
Hi all,
I am having issues with an update statement using a CASE expression:
UPDATE #TEMP1
SET Abstract_area =
CASE
WHEN EA.DataSet = 'Abstract' THEN EA.BufferedArea_km2
ELSE 999
END
-- END,
-- Discharges_area =
-- CASE EA.DataSet WHEN 'discharges' THEN EA.BufferedArea_km2
-- END
FROM #TEMP1 T
INNER JOIN Royalties.dbo.RoyaltyActuals EA
ON EA.OrderLineItemID = T.OrderLineItemID
WHERE EA.DataSet IN ('Abstract', 'discharges')
If the Where clause has multiple items in it, in most cases 999 is returned for column Abstract_area, if I remove "discharges" from the where clause and leave everything else the same, Abstract area is set correctly.
I have about 20 update statements all updating individual columns based on EA.Dataset, they are individual ones at the moment - I wanted to combine them all into one update statement using CASE, but can't seem to get it working. I've googled quite a bit and it seems that my syntax is legal - perhaps someone could suggest where I am going wrong please?
Just to confirm, WHERE EA.DataSet IN ('Abstract', 'discharges') means that Abstract_area is set to 999 (in most cases), and WHERE EA.DataSet IN ('Abstract') results in Abstract_area being set correctly - in ALL cases.
I haven't included any data/DDL as this is a general question on syntax, but happy to produce some if it helps.
thank you
D.
May 8, 2015 at 5:45 am
Try this:
UPDATE [temp]
SET Abstract_area = tmp.DataSet
FROM #TEMP1 [temp]
INNER JOIN ( SELECT T.* ,
CASE WHEN EA.DataSet = 'Abstract'
THEN EA.BufferedArea_km2
ELSE 999
END [DataSet]
FROM #TEMP1 T
INNER JOIN Royalties.dbo.RoyaltyActuals EA ON EA.OrderLineItemID = T.OrderLineItemID
WHERE EA.DataSet IN ( 'Abstract', 'discharges' )
) tmp ON [temp].OrderLineItemID = tmp.OrderLineItemID
Igor Micev,
My blog: www.igormicev.com
May 8, 2015 at 5:51 am
Igor Micev (5/8/2015)
Try this:
UPDATE [temp]SET Abstract_area = tmp.DataSet
FROM #TEMP1 [temp]
INNER JOIN ( SELECT T.* ,
CASE WHEN EA.DataSet = 'Abstract'
THEN EA.BufferedArea_km2
ELSE 999
END [DataSet]
FROM #TEMP1 T
INNER JOIN Royalties.dbo.RoyaltyActuals EA ON EA.OrderLineItemID = T.OrderLineItemID
WHERE EA.DataSet IN ( 'Abstract', 'discharges' )
) tmp ON [temp].OrderLineItemID = tmp.OrderLineItemID
Thank you for your suggestion Igor, unfortunately I get the same result, lots of 999's.. I also wanted to combine all of my updates in on update query, i.e. be able to update Abstract_area AND Discharges_Area based on a CASE expression of EA.DataSet.
Perhaps I should provide some sample data?
thanks again
D.
May 8, 2015 at 5:56 am
duncanburtenshaw (5/8/2015)
Igor Micev (5/8/2015)
Try this:
UPDATE [temp]SET Abstract_area = tmp.DataSet
FROM #TEMP1 [temp]
INNER JOIN ( SELECT T.* ,
CASE WHEN EA.DataSet = 'Abstract'
THEN EA.BufferedArea_km2
ELSE 999
END [DataSet]
FROM #TEMP1 T
INNER JOIN Royalties.dbo.RoyaltyActuals EA ON EA.OrderLineItemID = T.OrderLineItemID
WHERE EA.DataSet IN ( 'Abstract', 'discharges' )
) tmp ON [temp].OrderLineItemID = tmp.OrderLineItemID
Thank you for your suggestion Igor, unfortunately I get the same result, lots of 999's.. I also wanted to combine all of my updates in on update query, i.e. be able to update Abstract_area AND Discharges_Area based on a CASE expression of EA.DataSet.
Perhaps I should provide some sample data?
thanks again
D.
You can. Maybe it's the data.
Igor Micev,
My blog: www.igormicev.com
May 8, 2015 at 6:16 am
Structure your query as a select first. It's easier to debug.
SELECT
CASE
WHEN EA.DataSet = 'Abstract' THEN EA.BufferedArea_km2
ELSE 999
END as AbstractArea,
CASE EA.DataSet WHEN 'discharges' THEN EA.BufferedArea_km2
END as Discharge_Area
FROM #TEMP1 T
INNER JOIN Royalties.dbo.RoyaltyActuals EA
ON EA.OrderLineItemID = T.OrderLineItemID
WHERE EA.DataSet IN ('Abstract', 'discharges')
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
May 8, 2015 at 6:40 am
Any chance OrderLineItemID is not unique in RoyaltyActuals?
Then depending on where predicate different set of RoyaltyActuals rows will be applyed to update given #TEMP1.OrderLineItemID producing different results.
Demo
DECLARE @temp1 TABLE (
OrderLineItemID INT
,Abstract_area INT
,Discharges_area INT
);
INSERT @temp1 ( OrderLineItemID,Abstract_area,Discharges_area)
VALUES (1,0,0);
DECLARE @RoyaltyActuals TABLE (
OrderLineItemID INT
,BufferedArea_km2 INT
,DataSet VARCHAR(20)
);
INSERT @RoyaltyActuals (OrderLineItemID,BufferedArea_km2,DataSet)
VALUES (1,100,'discharges')
,(1,200,'Abstract');
SELECT *
FROM @RoyaltyActuals
UPDATE @temp1
SET Abstract_area = CASE
WHEN EA.DataSet = 'Abstract'
THEN EA.BufferedArea_km2
ELSE 999
END
,Discharges_area = CASE EA.DataSet
WHEN 'discharges'
THEN EA.BufferedArea_km2
END
FROM @TEMP1 T
INNER JOIN @RoyaltyActuals EA ON EA.OrderLineItemID = T.OrderLineItemID
WHERE EA.DataSet IN ('Abstract','discharges')
SELECT *
FROM @temp1;--999,100
UPDATE @temp1
SET Abstract_area = CASE
WHEN EA.DataSet = 'Abstract'
THEN EA.BufferedArea_km2
ELSE 999
END
,Discharges_area = CASE EA.DataSet
WHEN 'discharges'
THEN EA.BufferedArea_km2
END
FROM @TEMP1 T
INNER JOIN @RoyaltyActuals EA ON EA.OrderLineItemID = T.OrderLineItemID
WHERE EA.DataSet IN ('Abstract')
SELECT *
FROM @temp1;--200, NULL
May 8, 2015 at 7:11 am
What do you want the value of Abstract_Area to be when EA.DataSet='discharges'?
I think there's probably some confusion around what the query's supposed to be doing in that case.
Cheers!
May 8, 2015 at 7:21 am
thanks to everyone for their suggestions, on writing some test data I noticed that it was working ok, it must be my data.. I will close the call!
IF OBJECT_ID('tempdb.dbo.#DATA') IS NOT NULL
DROP TABLE #DATA
CREATE TABLE #DATA (
ORDERLINEITEMID int,
ABSTRACT_AREA decimal(10, 2)
)
INSERT INTO #DATA
VALUES (1878338, 0),
(1881073, 0)
IF OBJECT_ID('tempdb.dbo.#EA') IS NOT NULL
DROP TABLE #EA
CREATE TABLE #EA (
PK int,
ORDERLINEITEMID int,
BUFFEREDAREA_KM2 decimal(10, 6),
DATASET varchar(50)
)
INSERT INTO #EA
VALUES (12624174, 1881073, 12.718272, 'Abstract'),
(12624173, 1881073, 0.825355, 'discharges'),
(12652829, 1878338, 16.262663, 'Abstract'),
(12652828, 1878338, 1.847207, 'discharges')
UPDATE #DATA
SET Abstract_area =
CASE
WHEN EA.DataSet = 'Abstract' THEN EA.BufferedArea_km2
ELSE 999
END
FROM #DATA T
INNER JOIN #EA EA
ON EA.OrderLineItemID = T.OrderLineItemID
WHERE EA.DataSet IN ('Abstract', 'discharges')
SELECT
*
FROM #DATA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply