May 2, 2012 at 12:18 pm
I CREATED THE FOLLOWING TABLE.
CREATE TABLE SCRAMBLE
(FNAME VARCHAR(10),
LNAME VARCHAR(10)
)
INSERT INTO SCRAMBLE VALUES('APPLE','BROCOLLI')
INSERT INTO SCRAMBLE VALUES ('BANNANA','CARROT')
INSERT INTO SCRAMBLE VALUES ('PUMPKIN','POTATO')
INSERT INTO SCRAMBLE VALUES ('ORANGE','CABBAGE')
INSERT INTO SCRAMBLE VALUES ('PINEAPPLE','SPINACH')
INSERT INTO SCRAMBLE VALUES('PEERS','BRINJAL')
SELECT * FROM SCRAMBLE
Now, I would like to randomization of columns between first name and last name, i.e rows should be mis-matching.
So, I have run the following SELECT SCRIPT and it produced the result of what I have expected.
SELECTt1.FNAME,
t2.LName
FROM(
SELECTFNAME,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS recID
FROMSCRAMBLE
) AS t1
INNER JOIN(
SELECTLName,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS recID
FROMSCRAMBLE
) AS t2 ON t2.recID = t1.recID
o/p result.
FNAMELName
===================
PUMPKINCABBAGE
APPLESPINACH
PINEAPPLE CARROT
PEERSBROCOLLI
ORANGEPOTATO
BANNANABRINJAL
It basically varies according to number of executions we made.
My question is: How can I update with this SELECT STATEMENT INTO MY TABLE.
I Tried the following update statement, but it didn't worked.
update scramble
SET FNAME = T1.Fname,
LNAME =T2.Lname
FROM(
SELECTFNAME,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS recID
FROMSCRAMBLE
) AS t1
INNER JOIN(
SELECTLName,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS recID
FROMSCRAMBLE
) AS t2
ON t2.recID = t1.recID
o/p:
FNAMELNAME
=====================
PUMPKINSPINACH
PINEAPPLECARROT
PUMPKINSPINACH
PINEAPPLECARROT
PUMPKINSPINACH
PINEAPPLECARROT
Can you please help me with UPDATE statment,so that I can get some output similar to as mentioned with select statement. Thank you.
Let me know if you have questions.
May 2, 2012 at 12:29 pm
ok..i was not aware where exactly to post..thanks for info
May 2, 2012 at 12:38 pm
Ignoring the infinite loop about where to post.
Your update statement doesn't specify which row in Scramble matches which row in your random select.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2012 at 12:41 pm
GSquared (5/2/2012)
Ignoring the infinite loop about where to post.Your update statement doesn't specify which row in Scramble matches which row in your random select.
Gus, use the other one.
May 2, 2012 at 12:45 pm
What you need is something comparable to this:
IF OBJECT_ID(N'tempdb..#SCRAMBLE') IS NOT NULL
DROP TABLE #SCRAMBLE ;
CREATE TABLE #SCRAMBLE
(FNAME VARCHAR(10),
LNAME VARCHAR(10)) ;
INSERT INTO #SCRAMBLE
VALUES ('APPLE', 'BROCOLLI'),
('BANNANA', 'CARROT'),
('PUMPKIN', 'POTATO'),
('ORANGE', 'CABBAGE'),
('PINEAPPLE', 'SPINACH'),
('PEERS', 'BRINJAL')
SELECT *
FROM #SCRAMBLE ;
;
WITH CTE
AS (SELECT ROW_NUMBER() OVER (ORDER BY FNAME) AS R,
*
FROM #SCRAMBLE),
CTE2
AS (SELECT t1.FNAME,
t2.LName,
ROW_NUMBER() OVER (ORDER BY t1.FNAME) AS R2
FROM (SELECT FNAME,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS recID
FROM #SCRAMBLE) AS t1
INNER JOIN (SELECT LName,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS recID
FROM #SCRAMBLE) AS t2
ON t2.recID = t1.recID)
UPDATE CTE
SET LNAME = CTE2.LName, FNAME = CTE2.FNAME
FROM CTE
INNER JOIN CTE2
ON R = R2 ;
SELECT *
FROM #SCRAMBLE ;
It could be simplified. You don't actually need to do anything with the FNAME column, just the LNAME. You'll get the same level of randomization, unless there are other columns that need to stay static while the FNAME changes. But based on this two-column design, changing either one gives you a fully random mix.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2012 at 12:45 pm
Lynn Pettis (5/2/2012)
GSquared (5/2/2012)
Ignoring the infinite loop about where to post.Your update statement doesn't specify which row in Scramble matches which row in your random select.
Gus, use the other one.
Too late.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2012 at 12:47 pm
And this is why you shouldn't cross post. We have discussions and answers split across threads.
May 2, 2012 at 12:56 pm
Thank you so much. I actually need to mask the data. so i found this would be good option.
May 2, 2012 at 12:58 pm
DBA_SQL (5/2/2012)
Thank you so much. I actually need to mask the data. so i found this would be good option.
Figured as much.
Does the reason and solution make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2012 at 3:03 pm
Yes it makes sense. Thank you
May 2, 2012 at 10:00 pm
Why are we messing around with real names? Single update with random length GUIDs will mask the data with guaranteed no way back.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply