May 2, 2012 at 12:17 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:19 pm
Duplicate post...ignore this thread and instead view the one with an answer.
http://www.sqlservercentral.com/Forums/Topic1294098-1550-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 12:20 pm
Now we have a circular loop. Talk about fragmenting answers.
May 2, 2012 at 12:36 pm
OK let's use this one then.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 12:39 pm
DBA_SQL (5/2/2012)
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
What do you mean it didn't work? I ran that and it worked fine. It updated the scramble table with pretty much exactly the same as your select statement did.
Now I think that perhaps the real issue is that your scrambling code is not doing what you want it to do?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 12:46 pm
Exact reason for the problem and solution to it posted on the other copy of this thread.
- 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:48 pm
And this is why you shouldn't cross post. We have discussions and answers split across threads.
May 2, 2012 at 12:53 pm
Why dont you delete (or truncate) the table and then insert the output?
You are trying to randomize each row , create new combinations and replace each row.. So why not truncate and load?
May 2, 2012 at 9:59 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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply