how to achieve without loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to achieve without loop

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2010-12-09 : 02:12:08

Hi,
i have 2 tables
Table 1
id col1 Col2
1 3AC15A0F47
2 6C19B7D87C
3 B839138E37
4 6E76C3CF56
5 FDE25B9037
6 86677BE40B
7 5F82D1EF33
8 1266443821
9 5195944156
10 869A513284

Table 2
col2
Ken Sánchez
Terri Duffy
Roberto Tamburello

i want result like this

id col1 Col2
1 3AC15A0F47 Ken Sánchez
2 6C19B7D87C Terri Duffy
3 B839138E37 Roberto Tamburello
4 6E76C3CF56 Ken Sánchez
5 FDE25B9037 Terri Duffy
6 86677BE40B Roberto Tamburello
7 5F82D1EF33 Ken Sánchez
8 1266443821 Terri Duffy
9 5195944156 Roberto Tamburello
10 869A513284 Ken Sánchez

i need to achieve this without a loop. blindly i have to update table 2 col2 3 recoreds in table 1,please advice

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-09 : 03:07:24

There is NO visible relation between table1 and table2.
If you want help then give the correct table structure with sample data and wanted result please.

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-09 : 03:10:41

What I understand is that for first record in table 1, a first record from table 2 needs to be selected / updated.

When table 2 has less records than 1, the cycle will again start from 1 record of table 2.

I remember that I have seen a solution by Peso in the past for the same requirement using CTE.

I fairly remember:

Using CTE get Srno for table 1.

update table1 set column =
When Srno % 3 = 1 then 'FirstUpd'
when Srno % 3 = 2 then 'SecUpd'
else
'ThirdUpdate'
End.

shaggy
Posting Yak Master

248 Posts

Posted - 2010-12-09 : 05:07:59

thanks for all your replies

yes there is no relation between tables

my requirement ,table1 have some millions of records. in table 2 i have 1000's of records

in table 1 i got one column loginname i need to randomly update the column by getting record from table2 (1000 records should be updated in all million records)

this can be achieved by looping ,since the data will grow larger i am looking for the alternates.

again thanks all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-09 : 12:02:49

[code]UPDATE t
SET t.loginname= t1.val
FROM table t
CROSS APPLY (SELECT TOP 1 val
FROM table2
ORDER BY NEWID()) t1
[/code]

replace val with actual column of table 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-09 : 12:21:12

You could do as pk_bohra suggested and use a case expression. That would probably be more accurate, if you need to force the order. Another option might be to do something like:

DECLARE @Table1 TABLE (ID INT, Col1 VARCHAR(30), Col2 VARCHAR(30))
INSERT @Table1 (ID, Col1) VALUES
(1, '3AC15A0F47'),
(2, '6C19B7D87C'),
(3, 'B839138E37'),
(4, '6E76C3CF56'),
(5, 'FDE25B9037'),
(6, '86677BE40B'),
(7, '5F82D1EF33'),
(8, '1266443821'),
(9, '5195944156'),
(10, '869A513284')

DECLARE @Table2 TABLE (Col2 VARCHAR(30))

INSERT @Table2 (Col2) VALUES
('Ken Sánchez'),
('Terri Duffy'),
('Roberto Tamburello')

UPDATE
    T1
SET
    Col2 = D.Col2
FROM
    @Table1 AS T1
INNER JOIN
    (
        SELECT 
            Col2,
            ROW_NUMBER() OVER(ORDER BY Col2) AS RowNum
        FROM
            @Table2 AS Table2
    ) AS D    
    ON
    T1.ID % 3 = D.RowNum - 1

shaggy
Posting Yak Master

248 Posts

Posted - 2010-12-10 : 10:21:50

Thanks Lamprey & vishak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-11 : 00:24:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources