CODEWITHSUNDEEP
t-sqlvariablessql-update
EbertB
EbertB

Reputation: 103

Update Variable based on Group

I need to perform an update to a field in a table with a variable, but I need the variable to change when the group changes. It is just an INTt, so for example if I The example below I want to update the record of texas with a 1 and flordia with the next number of 2:

UPDATE table set StateNum = @Count FROM table where xxxxx GROUP BY state

Group Update Variable Texas 1 Texas 1 Florida 2
Florida 2 Florida 2

Upvotes: 0

Views: 20

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

I think you should use a lookup table with the state and its number StateNum Then you should store this number instead of the name to your table.

You might use DENSE_RANK within an updateable CTE:

--mockup data

DECLARE @tbl TABLE([state] VARCHAR(100),StateNum INT);
INSERT INTO @tbl([state]) VALUES
('Texas'),('Florida'),('Texas'),('Nevada');

--your update-statement

WITH updateableCTE AS
(
    SELECT StateNum
          ,DENSE_RANK() OVER(ORDER BY [state]) AS NewValue
    FROM @tbl 
)
UPDATE updateableCTE SET StateNum=NewValue;

--check the result

SELECT * FROM @tbl;

And then you should use this to get the data for your lookup table

SELECT StateNum,[state] FROM @tbl GROUP BY StateNum,[state];

Then drop the state-column from your original table and let the StateNum be a foreign key.

Upvotes: 1

Related Questions