2

SQL Server UPDATE statement to switch values between random rows in all the rows on the table.

I need to switch values from an specific column between rows for all the rows on the table. Which row change values with which other row on the table is totally Ramdow. I'm doing this on Oracle, SQL Server and DB2.

Example:

Before update:

ID - Name - LastName
--------------------
1 - Peter - Gonzalez
2 - Max - Green
... 
4480 - Maximus - Magna
4495 - Martha - Potter

After update:

1- Martha - Gonzalez
2- William - Green
...
4480 - Peter - Magna
4495 - otherNameFromAnotherRow - Potter

I checked this related question:

I tried to combine the logic of doing a random order with the Update of a row with data of the next row, but I couldn't make it right: 

UPDATE HR.PERSONS 
SET (NAME) = SELECT NAME 
             FROM 
                 (SELECT t1.NAME 
                  FROM PERSONS t1 
                  ORDER BY dbms_random.value) 
WHERE t1.ROWID > PERSONS.ROWID

AND 

UPDATE HR.PERSONAS 
SET (NOMBRE) = (SELECT NOMBRE 
                FROM 
                    (SELECT t1.NOMBRE 
                     FROM PERSONAS t1  
                     ORDER BY dbms_random.value) 
                 WHERE ROWNUM = 1)

In this case, the subquery just get one of the values, and set that value to all the rows.

This examples are for Oracle (They doesn't work on oracle), I'm trying to make that query right and accomplish the same in SQL Server and DB2 too).

Cœur
  • 37,241
  • 25
  • 195
  • 267
Kail Alexander
  • 23
  • 3
  • For sql server you would need to use ROW_NUMBER and order by select newid(). The syntax for DB2 is going to be different also. – Sean Lange Jul 30 '18 at 21:32

1 Answers1

0
update myTable
   set Name = shuffle.Name
from myTable t
inner join 
(select id, row_number() over (order by id) as origId from mytable) orig
on orig.id = t.id
join 
(select Name, row_number() over (order by newid()) as [newId] from myTable) shuffle
on orig.OrigId = shuffle.[newId];
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • This is for Oracle. Am I Right? In this answer you talk about an ID Column but, in the schema it is possible that doesn't exist and ID Column. There's a way to do it without ID? – Kail Alexander Jul 30 '18 at 22:05
  • I tried to execute that query on Oracle with DBeaver with the correct table name, and columns, and I get this error: Query execution failed Reason: SQL Error [933] [42000]: ORA-00933: SQL command not properly ended Also if I try to run just this part, I get an error: (select Name, row_number() over (order by newid()) as [newId] from myTable) Saying that the from was not where expected. (Maybe there is the problem). – Kail Alexander Jul 30 '18 at 22:13
  • @KailAlexander, no it is for SQL Server. Id is meant to be the primary key (every table has one, no?). – Cetin Basoz Jul 30 '18 at 22:25
  • I see, thanks, I would try it on SQL server, My question sounds like I was asking the equivalente of oracle on sql server, but actually I'm trying 2 things: Make it right on oracle (because my query examples doesn't work) and do the same on sql server. Your answer help me with Sql Server, but I still have the problem with Oracle. (I've changed my question to make it clear). – Kail Alexander Jul 31 '18 at 19:12