update table 1 from table2 - 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

SQL Server 2005 Forums

Transact-SQL (2005)

update table 1 from table2

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-11-02 : 04:11:48

Hi,

I want to update table 1 from table 2. Below is the out put which I need.

SELECT TOP 5 * FROM SG89411.SW_INSTALLS_11022011

create table #table1
(product varchar(100), match int)

insert into #table1 values ('adobe',null)
insert into #table1 values ('adobe',null)
insert into #table1 values ('citrix',null)
insert into #table1 values ('citrix',null)
insert into #table1 values ('citrix',null)

create table #table2
(product varchar(100), idno int)

insert into #table2 values ('adobe',1)
insert into #table2 values ('adobe',2)
insert into #table2 values ('adobe',3)
insert into #table2 values ('citrix',1)
insert into #table2 values ('citrix',3)

update #table1
set match = t2.idno
from #table1 t1 join #table2 t2
on t1.product = t2.product

need out put as:

product match
adobe 1
adobe 2
citrix 1
citrix 3
citrix null

Regards,
Sachin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-02 : 04:22:55

[code]
update t1
set match = t2.idno
from (
select *, rn = row_number() over (partition by product order by product)
from #table1
) t1
inner join
(
select *, rn = row_number() over (partition by product order by idno)
from #table2
) t2 on t1.product = t2.product
and t1.rn = t2.rn
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-11-02 : 04:48:36

using the above update statement, i am getting below output.

product match
adobe 1
adobe 2
citrix 1
citrix 2
citrix 1

I should get

product match
adobe 1
adobe 2
citrix 1
citrix 3
citrix null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-02 : 04:53:26

identical query, no changes at all ?

KH
[spoiler]Time is always against us[/spoiler]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-11-02 : 05:20:15

Thank you it worked now.

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-11-03 : 00:23:12

I am looking for a scenario like below:

TABLE 1
vendor product match
adobe reader
adobe reader
microsoft word
microsoft word
IBM word
crystal reader

TABLE2

vendor product entid status
adobe reader 1
adobe reader 2
adobe reader 3
microsoft word 4
IBM word 5
unknown reader 6

1. first match vendor columns of both tables and update both tables: populate entid of table 2 in match column of table1 and status of table2 will be populated as used which means that row is used and should not be used further.
2. then second match should be on product

final output of both tables:

TABLE 1
vendor product match
adobe reader 1
adobe reader 2
microsoft word 4
microsoft word null
IBM word 5
crystal reader 3

TABLE 2
vendor product entid status
adobe reader 1 used
adobe reader 2 used
adobe reader 3 used
microsoft word 4 used
IBM word 5 used
unknown reader 6 null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 00:27:09

[code]
update t1
set match = t2.idno
from (
select *, rn = row_number() over (partition by vendor, product order by product)
from #table1
) t1
inner join
(
select *, rn = row_number() over (partition by vendor, product order by idno)
from #table2
) t2 on t1.product = t2.product
and t1.vendor = t2.vendor
and t1.rn = t2.rn
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-11-03 : 00:39:46

my first update should be like below

-- first update
update table1 join table2 on vendor = vendor

-- second update
update table1 join table2 on product = product where table 2 rows are not used in first update

the objective here is update unique id of table 2 into table1 using matching criteria and simultaneosly mark the row of table2 as "used" so that it is not used for further updates (means not using unique entid again).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 00:43:23

can you post some sample data and the result after 1st and 2nd update ?

KH
[spoiler]Time is always against us[/spoiler]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2011-11-03 : 00:54:15

BEFORE

TABLE1
vendor product match
adobe reader NULL
adobe reader NULL
microsoft word NULL
microsoft word NULL
IBM word NULL
crystal reader NULL

TABLE2
vendor product entid status
adobe reader 1 NULL
adobe reader 2 NULL
adobe reader 3 NULL
microsoft word 4 NULL
IBM word 5 NULL
unknown reader 6 NULL

AFTER UPDATE
--------------------------------------

TABLE1
vendor product match
adobe reader 1
adobe reader 2
microsoft word 4
microsoft word null
IBM word 5
crystal reader 3

TABLE2
vendor product entid status
adobe reader 1 used
adobe reader 2 used
adobe reader 3 used
microsoft word 4 used
IBM word 5 used
unknown reader 6 null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-03 : 02:44:45

[code]
declare @used table
(
entid int
)

-- UPDATE 1 by vendor
update t1
set match = t2.entid
output t2.entid
into @used (entid)
from (
select *, rn = row_number() over (partition by vendor order by vendor)
from #table1
) t1
inner join
(
select *, rn = row_number() over (partition by vendor order by entid)
from #table2
where status is null
) t2 on t1.vendor = t2.vendor
and t1.rn = t2.rn

update t2
set status = 'used'
from #table2 t2
inner join @used u on t2.entid = u.entid
where t2.status is null

-- UPDATE 2 by product

update t1
set match = t2.entid
output t2.entid
into @used (entid)
from (
select *, rn = row_number() over (partition by product order by product)
from #table1
where match is null
) t1
inner join
(
select *, rn = row_number() over (partition by product order by entid)
from #table2
where status is null
) t2 on t1.product = t2.product
and t1.rn = t2.rn

update t2
set status = 'used'
from #table2 t2
inner join @used u on t2.entid = u.entid
where t2.status is null
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources