TSQL : Update columns with different values randomly

Continuing the series on SQL challenges. I came across a requirement from an online Tech forum where the request was to perform an update on a table column with a sequence of values randomly I have tried to write a SQL query to perform this update. Please review the query below, and use it when it you have such requirement. Also review and provide your comments for any improvisation

DECLARE @Deptrans TABLE (
	ID INT IDENTITY(1, 1)
	,Names VARCHAR(50)
	);
DECLARE @logic TABLE (
	ID INT
	,NewNames VARCHAR(50)
	);

INSERT INTO @Deptrans (Names)
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all 
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' 


INSERT INTO @Logic (ID, NewNames)
SELECT 1, 'Name1' union all
SELECT 2, 'Name2' union all
SELECT 3, 'Name3' union all
SELECT 0, 'Name4'


SELECT *
FROM @Deptrans

;WITH Setss
AS (
	SELECT ID % 4 ROWN
		,ID
		,Names
	FROM @Deptrans
	)
UPDATE ss
SET Names = lo.NewNames
FROM Setss ss
JOIN @logic lo ON ss.ROWN = lo.ID

SELECT *
FROM @Deptrans

Output:

This entry was posted in SQL SERVER 2008, T-SQL and tagged , , , . Bookmark the permalink.

1 Response to TSQL : Update columns with different values randomly

  1. Moises says:

    Your website has outstanding material. I bookmarked the site

Leave a Reply

Your email address will not be published. Required fields are marked *