need help on updating data
-
Hello friends, I need a help on updating the data into a column based on values in another column in the same table. below is a part part the table:
As you can see the products are grouped using the same ID. What I want to do is that within each group, I want the Level returns "80" as its associated Rate is the smallest in the group and "100" if the rate is the the largest.
For instance, I want the first row with the ID of 1 to return "80" in level column since 0.01000 is the smallest number among the group (others are 0.02000, 0.03000,0.04000, 0.05000) and the last row in the group to return "100" as 0.05000 is the largest number in the rate list of the group. same idea should apply to all other grouped products.
Of course I can manually update the column by using UPDATE statement for each group one at the time but it would be very time-consuming and I would most likely end up with a lot mistakes.
Please help me solve this problem, preferably with one single sql / T-sql statement that would apply to the whole table. Thank you very much in advance!
-
Given that you did not post any DDL, how about this as a solution. You will need to tweak it to suit your code:
USE tempdb
GO
CREATE TABLE TestData (
ID INT,
PRODUCT NVARCHAR(100),
ProdID int,
STRTAGE int,
ENDAGE int,
LEVEL NVARCHAR(10) NULL,
RATE decimal(10,2)
)
INSERT INTO TestData
SELECT 1, 'Blahblah',1234,0,100,NULL, 0.01 UNION ALL
SELECT 1, 'Blahblah',1234,0,100,85, 0.02 UNION ALL
SELECT 1, 'Blahblah',1234,0,100,90, 0.03 UNION ALL
SELECT 1, 'Blahblah',1234,0,100,95, 0.04 UNION ALL
SELECT 1, 'Blahblah',1234,0,100,100, 0.04 UNION ALL
SELECT 100, 'TestTest',1234,0,100,NULL, 0.08 UNION ALL
SELECT 100, 'TestTest',1234,0,100,85, 0.09 UNION ALL
SELECT 100, 'TestTest',1234,0,100,90, 0.10 UNION ALL
SELECT 100, 'TestTest',1234,0,100,95, 0.11 UNION ALL
SELECT 100, 'TestTest',1234,0,100,100, 0.12
SELECT * FROM TestData
;WITH CTE_UPDATES AS (
SELECTID,
MIN(RATE) as MinRow,
MAX(RATE) As MaxRow
FROM TestData
GROUP BY ID)
UPDATE A
SET A.Level =CASEWHEN A.Rate = B.MinRow THEN '80'
WHEN A.Rate = B.MaxRow THEN '100'
END
FROM TestData A
JOIN CTE_UPDATES AS B
ON A.ID = B.ID
AND A.Rate = B.MinRow;
SELECT * FROM TestData
MCITP SQL 2005, MCSA SQL 2012
-
Actually found a few flaws in my first attempt, try this:
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'TestData')
DROP TABLE TestData
GO
CREATE TABLE TestData (
ID INT,
PRODUCT NVARCHAR(100),
ProdID int,
STRTAGE int,
ENDAGE int,
LEVEL NVARCHAR(10) NULL,
RATE decimal(10,2)
)
GO
INSERT INTO TestData
SELECT 1, 'Blahblah',1234,0,100,NULL, 0.01 UNION ALL
SELECT 1, 'Blahblah',1234,0,100,85, 0.02 UNION ALL
SELECT 1, 'Blahblah',1234,0,100,90, 0.03 UNION ALL
SELECT 1, 'Blahblah',1234,0,100,95, 0.04 UNION ALL
SELECT 1, 'Blahblah',1234,0,100,NULL, 0.05 UNION ALL
SELECT 100, 'TestTest',1234,0,100,NULL, 0.08 UNION ALL
SELECT 100, 'TestTest',1234,0,100,85, 0.09 UNION ALL
SELECT 100, 'TestTest',1234,0,100,90, 0.10 UNION ALL
SELECT 100, 'TestTest',1234,0,100,95, 0.11 UNION ALL
SELECT 100, 'TestTest',1234,0,100,NULL, 0.12
GO
SELECT * FROM TestData
;WITH CTE_UPDATES AS (
SELECTID,
MIN(RATE) as MinRow,
MAX(RATE) As MaxRow
FROM TestData
GROUP BY ID)
UPDATE A
SET A.Level =CASEWHEN A.Rate = B.MinRow THEN '80'
WHEN A.Rate = B.MaxRow THEN '100'
ELSE A.Level
END
FROM TestData A
JOIN CTE_UPDATES AS B
ON A.ID = B.ID ;
SELECT * FROM TestData
MCITP SQL 2005, MCSA SQL 2012
-
RTaylor2208, Thank you so much for your quick response. I will try it.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply