November 5, 2021 at 5:17 am
Hello All,
I currently have a table with the DDL below;
CREATE TABLE #Test (Id INT, CarID INT, CarStatus CHAR(1));
INSERT INTO #Test (Id, CarId, CarStatus)
SELECT 1, 1000, 1 UNION ALL
SELECT 2, 2000, 1 UNION ALL
SELECT 3, 3000, 1 UNION ALL
SELECT 4, 3000, 1 UNION ALL
SELECT 5, 2000, 1 UNION ALL
SELECT 6, 1000, 1 UNION ALL
SELECT 7, 1000, 1 UNION ALL
SELECT 8, 2000, 1 UNION ALL
SELECT 9, 3000, 1;
I would like to update the CarStatus to 2 but for only the distinct values of CarId. Notice the CarId repeats between 1000, 2000, 3000.
I only need to update the CarStatus for the first 3 unique CarId values. Result should look something like this
CREATE TABLE #TestOutput (Id INT, CarID INT, CarStatus CHAR(1));
INSERT INTO #TestOutput (Id, CarId, CarStatus)
SELECT 1, 1000, 2 UNION ALL
SELECT 2, 2000, 2 UNION ALL
SELECT 3, 3000, 2 UNION ALL
SELECT 4, 3000, 1 UNION ALL
SELECT 5, 2000, 1 UNION ALL
SELECT 6, 1000, 1 UNION ALL
SELECT 7, 1000, 1 UNION ALL
SELECT 8, 2000, 1 UNION ALL
SELECT 9, 3000, 1;
Thank you
November 5, 2021 at 7:54 am
This will work with the sample data that you provided
WITH cteData AS (
SELECT t.Id
, t.CarID
, t.CarStatus
, rn = ROW_NUMBER() OVER (PARTITION BY t.CarID ORDER BY t.Id)
FROM #Test AS t
)
UPDATE cteData
SET cteData.CarStatus = '2'
WHERE rn = 1;
November 5, 2021 at 9:23 am
This should work, I think:
UPDATE t
SET t.CarStatus = '2'
FROM #Test t
INNER JOIN (
SELECT CarID, MIN(Id) AS Id
FROM #Test
GROUP BY CarID
HAVING COUNT(*) > 1
) t2 ON t2.Id=t.Id
November 5, 2021 at 6:26 pm
Thank you. Both solutions worked very well.
November 6, 2021 at 8:10 pm
This should work, I think:
UPDATE t
SET t.CarStatus = '2'
FROM #Test t
INNER JOIN (
SELECT CarID, MIN(Id) AS Id
FROM #Test
GROUP BY CarID
HAVING COUNT(*) > 1
) t2 ON t2.Id=t.Id
Careful now... that's only going to mark cars that have more than one occurrence. An "only" occurrence is still a "first" occurrence. Your code won't find an "only"first occurrence.
Of course, that depends on what the OP actually needs but that's what I got out of his definition of the problem even though not explicitly stated so.
Try your code with the following data and see what I mean.
DROP TABLE IF EXISTS #Test;
CREATE TABLE #Test (Id INT, CarID INT, CarStatus CHAR(1));
INSERT INTO #Test (Id, CarId, CarStatus)
SELECT 0, 4000, 1 UNION ALL --Should be a 2 by definition because it's the first occurance of 4000.
SELECT 1, 1000, 1 UNION ALL
SELECT 2, 2000, 1 UNION ALL
SELECT 3, 3000, 1 UNION ALL
SELECT 4, 3000, 1 UNION ALL
SELECT 5, 2000, 1 UNION ALL
SELECT 6, 1000, 1 UNION ALL
SELECT 7, 1000, 1 UNION ALL
SELECT 8, 2000, 1 UNION ALL
SELECT 9, 3000, 1 UNION ALL
SELECT 10, 5000, 1 --Should be a 2 by definition because it's the first occurance of 5000.
;
Your code also makes two scans of the table instead of just one.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2021 at 8:18 am
kaj wrote:This should work, I think:
UPDATE t
SET t.CarStatus = '2'
FROM #Test t
INNER JOIN (
SELECT CarID, MIN(Id) AS Id
FROM #Test
GROUP BY CarID
HAVING COUNT(*) > 1
) t2 ON t2.Id=t.IdCareful now... that's only going to mark cars that have more than one occurrence. An "only" occurrence is still a "first" occurrence. Your code won't find an "only"first occurrence.
Of course, that depends on what the OP actually needs but that's what I got out of his definition of the problem even though not explicitly stated so.
Try your code with the following data and see what I mean.
DROP TABLE IF EXISTS #Test;
CREATE TABLE #Test (Id INT, CarID INT, CarStatus CHAR(1));
INSERT INTO #Test (Id, CarId, CarStatus)
SELECT 0, 4000, 1 UNION ALL --Should be a 2 by definition because it's the first occurance of 4000.
SELECT 1, 1000, 1 UNION ALL
SELECT 2, 2000, 1 UNION ALL
SELECT 3, 3000, 1 UNION ALL
SELECT 4, 3000, 1 UNION ALL
SELECT 5, 2000, 1 UNION ALL
SELECT 6, 1000, 1 UNION ALL
SELECT 7, 1000, 1 UNION ALL
SELECT 8, 2000, 1 UNION ALL
SELECT 9, 3000, 1 UNION ALL
SELECT 10, 5000, 1 --Should be a 2 by definition because it's the first occurance of 5000.
;Your code also makes two scans of the table instead of just one.
Granted. But my code does mark the first of a non-unique group of rows. That was what I got out of the OPs description. Why else put the word unique in there. In effect I thought he wanted to mark all but the latest CarID row with a two, so that only the latest entry for each CarID has a CarStatus of one.
As for the two scans: Guilty as charged! So if this becomes a big table and there isn't a decent index on the table, it might become a problem.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy