Not going to go through what you're trying to accomplish, but for your update, you're using a TOP
without an ORDER BY
. While SQL Server doesn't guarantee ordering for such queries, it doesn't just randomly choose, either. If you want to force a random row to be returned, you'll need to order by a row-level random number. For that, you can use ORDER BY ABS( CHECKSUM( NEWID() ) )
.
Try it out against sys.objects
to see the behavior of explicitly ordering randomly.
SELECT TOP 1 name
FROM sys.objects
ORDER BY ABS( CHECKSUM( NEWID() ) );
And good luck with whatever it is you're doing.
Edit:
@jean points out in the comments just NEWID()
is all that is necessary, which is 100% correct, I just muscle-memory the ABS( CHECKSUM() )
since I tend to need row-level randomization with a %
operator.
Edit 2:
So something like this, then?
UPDATE t
SET CategoryID = nt.CategoryID
FROM dbo.Topic t
INNER JOIN ( SELECT t.ID, ucl.CategoryID,
Ordinal = ROW_NUMBER() OVER (
PARTITION BY t.ID
ORDER BY NEWID() )
FROM dbo.Topic t
INNER JOIN dbo.UserCategoryLink ucl
ON t.UserID = ucl.UserID ) nt
ON t.ID = nt.ID
WHERE nt.Ordinal = 1;
This should update for you as expected. Apologies for the number of character functions - I'm sure it could be improved via a REGEXP_SUBSTR call but I'm afraid I'm a bit busy at the moment.
MERGE INTO olddata old
USING (SELECT * FROM newdata) new
ON ( old.ename = new.ename
OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'.'),length(old.ename)))) = new.ename)
WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job
The first qualifier in the ON clause will match any that have the same format name and the second will take the string from OLDDATA.ENAME before the first dot to compare it to the NEWDATA.ENAME that doesn't contain the domain name.
Hope this works for you (it did for my quick tests on the data you supplied)
EDIT:
Here's my simple test:
1* SELECT * FROM olddata
SQL> /
EMPNO ENAME JOB SALARY HIREDATE
---------- ------------------------------ -------------------- ---------- ---------
7369 smith.domain.com clerk 1300 17-DEC-80
7645 jones.domain.com Manager 5000 01-JAN-80
7549 allen.domain.com sales 1800 01-JAN-81
SQL> SELECT * FROM newdata
2 /
ENAME JOB SALARY
------------------------------ -------------------- ----------
jones.domain.com Manager 6000
allen sales 2000
smith clerk 2000
SQL> MERGE INTO olddata old
2 USING (SELECT * FROM newdata) new
3 ON ( old.ename = new.ename
4 OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'.'),length(old.ename)))) = new.ename)
5 WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job
SQL> /
3 rows merged.
SQL> SELECT * FROM olddata
2 /
EMPNO ENAME JOB SALARY HIREDATE
---------- ------------------------------ -------------------- ---------- ---------
7369 smith.domain.com clerk 2000 17-DEC-80
7645 jones.domain.com Manager 6000 01-JAN-80
7549 allen.domain.com sales 2000 01-JAN-81
SQL>
EDIT: Thanks to @Guarava for pointing out that the string operations can easily be removed. I was just overthinking it. The following MERGE is much more concise;
MERGE INTO olddata old
USING (SELECT * FROM newdata) new
ON ( old.ename = new.ename
OR old.ename LIKE new.ename||'.%')
WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job
Best Answer
One way would be
Or a more concise alternative