CODEWITHSUNDEEP
sqlsql-servert-sql
user1774042
user1774042

Reputation: 3

update each row with different values in temp table

I have a column that I need to update with codes in a temp table.There is +/- 5 codes in the temp table. Each row in the table must be updated with one of the values in the temp table.

Example
id  code
1   200
2   400
3   600
4   9000
5   800
6   200
7   400
8   600
9   9000
10  800

Upvotes: 0

Views: 3811

Answers (3)

xray1986
xray1986

Reputation: 1168

A Different Approach: Lets say the table that you want to Update is called Example. A simple Oracle Merge would be.

Merge INTO Example Using Temp_Table ON Example.Id = Temp_Table.Id
When Matched Then
UPDATE Example
SET Example.code = Temp_Table.code
From Example
Inner Join
Temp_Table
ON
Example.Id = Temp_Table.Id
When Not Matched Then
Insert Into Example 
Select * From Temp_Table

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

SQL Server Solution

This query will sequentially take the values from the temp table and update the code in the example table in round robin fashion, repeating the values from temp when required.

update e
set code = t.code
from example e
join temp t on t.id = (e.id -1) % (select count(*) from temp) + 1

If the ids are not sequential in either table, then you can row_number() them first, e.g.

update e
set code = t.code
from (select *,rn=row_number() over (order by id) from example) e
join (select *,rn=row_number() over (order by id) from temp) t
  on t.rn = (e.rn -1) % (select count(*) from temp) + 1

The same technique (mod, row-number) can be used in other RDBMS, but the syntax will differ a little.

Upvotes: 0

roman
roman

Reputation: 117345

To get rowset you need try this

declare @cnt int

select @cnt = count(*) from codes

select M.id, C.Code
from
(
    select
        T.id, (row_number() over (order by id) - 1) % @cnt + 1 as RowNum
    from T as T
) as M
    left outer join
    (select Code, row_number() over (order by Code) as RowNum from codes as T) as C
        on C.RowNum = M.RowNum

http://sqlfiddle.com/#!3/cbd84/1

Upvotes: 1

Related Questions