October 21, 2008 at 10:23 am
Hello everybody,
how would I achieve that values in two columns are randomly exchanged? E.g. there is a table containing ID, LAST_NAME and FIRST_NAME columns.
I need first names to be randomly "re-assigned" to a different last name as its original one. What I thought is to have two cursors: one beginning at the end (highest ID) and going "backward" and the other beginning at the at the start (lowest ID) and going forward and just exchanging the first names for the 2 current rows in the cursors.
How could I achieve this?
Many thanks....
October 21, 2008 at 11:20 am
See if this does it, I put in 50 percent (so half will be swapped)
DECLARE @test-2 TABLE (
FirstName VARCHAR(MAX),
LastName VARCHAR(MAX),
ID INT IDENTITY(1,1)
)
INSERT INTO @test-2
SELECT '1','2' UNION ALL
SELECT '1','2' UNION ALL
SELECT '1','2' UNION ALL
SELECT '1','2' UNION ALL
SELECT '1','2' UNION ALL
SELECT '1','2'
UPDATE @test-2
SET LastName = FirstName,
FirstName = LastName
WHERE ID IN (SELECT TOP 50 PERCENT ID
FROM @test-2
ORDER BY NEWID()
)
SELECT * FROM @test-2
October 21, 2008 at 11:43 am
I don't think that's quite what the original post asked for, I think he wants to exchange first_name values from different rows. If it doesn't need to be an exact swap and can be a completely random reassignment, how about:
UPDATE o SET
o.first_name = n.first_name
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ABS(CHECKSUM(NEWID()))) AS rid, id, first_name FROM #nametable) o
INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY ABS(CHECKSUM(NEWID()))) AS rid, id, first_name FROM #nametable) n ON o.rid = n.rid
October 22, 2008 at 9:21 am
Hello Jerry/Chris,
many thanks for your responses! Chris' solution is exactly what I was looking for: nice, lean & sleek, even with more randomization provided.
I have also succeeded in the meantime, here's my approach:
[font="Courier New"]EXCHANGE WITH 2 CURSORS BEGINNING SIMULTANEOUSLY FROM START AND FROM END
declare
@cust1_id int,
@cust2_id int,
@cust2_sex int,
@cust1_LName varchar(50),
@cust2_LName varchar(50),
@cust1_FName varchar(50),
@cust2_FName varchar(50)
-- getting CUSTOMER records ascending by CUST_ID
declare obfuscator cursor static for
select CUST_ID, CUST_LASTNAME, CUST_FIRSTNAME from CUSTOMER order by CUST_ID
-- getting CUSTOMER records descending by CUST_ID
declare obfuscator2 cursor static for
select CUST_ID, CUST_LASTNAME, CUST_FIRSTNAME, CUST_SEX from CUST order by CUST_ID DESC
open obfuscator
-- fetching "forward"
fetch next from obfuscator into @cust1_id, @cust1_LName, @cust1_Fname
while @@FETCH_STATUS = 0
BEGIN
open obfuscator2
-- fetching "backward"
fetch next from obfuscator2 into @cust2_id,@cust2_LName, @cust2_Fname, @cust2_sex
while @@FETCH_STATUS = 0
BEGIN
-- check if cursors have reached the same CUST_ID (if odd number of customers is in the database):
IF @cust1_id = @cust2_id
UPDATE CUSTOMER SET CUST_FIRSTNAME = 'GEOFFREY', CUST_SEX=1 WHERE CUST_ID = @cust1_id
else
UPDATE CUSTOMER SET CUST_FIRSTNAME = @cust2_FName, CUST_SEX=@cust2_sex WHERE CUST_ID = @cust1_id
fetch next from obfuscator into @cust1_id, @cust1_LName, @cust1_Fname
fetch next from obfuscator2 into @cust2_id,@cust2_LName, @cust2_Fname,@cust_sex
END
END
close obfuscator2
deallocate obfuscator2
close obfuscator
deallocate obfuscator [/font]
Thanks very much again!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply