Update query help - 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

Update query help

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2010-07-14 : 23:10:09

sql 2000

i have three tables

1- create table mytab(id_no varchar(5), Counter smallint))
2- create table Rectab(id_no varchar(5), refno smallint))
3- create table Lib_tab(refno smallint)

insert into mytab values(1,null)
insert into mytab values(2,null)
insert into mytab values(3,null)
insert into mytab values(4,null)

-- Rectab has duplicate values

insert into Rectab values(1,1)
insert into Rectab values(1,1)
insert into Rectab values(1,2)
insert into Rectab values(1,2)
insert into Rectab values(1,2)
insert into Rectab values(1,3)
insert into Rectab values(2,1)
insert into Rectab values(2,1)
insert into Rectab values(3,2)
insert into Rectab values(3,2)
insert into Rectab values(3,2)
insert into Rectab values(3,3)
insert into Rectab values(4,3)
insert into Rectab values(4,3)
insert into Rectab values(4,2)
insert into Rectab values(4,1)
insert into Rectab values(4,1)

---Libtab Library table

insert into Lib_tab values(1)
insert into Lib_tab values(2)
insert into Lib_tab values(3)
insert into Lib_tab values(4)
insert into Lib_tab values(5)

i want to update mytab in manner that i want to get distinct count
(distinct id_no,refno) of rectab in counter column of my tab, vALUES of
refno MUST EXIST IN Lib_tab e.g out put should like

select * from mytab

id_no counter
1 3
2 1
3 2
4 3

what will be the update query

regards

Farid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-14 : 23:28:14

[code]
UPDATE m
SET Counter = c.cnt
FROM mytab m
INNER JOIN
(
SELECT id_no, cnt = COUNT(DISTINCT refno)
FROM Rectab r
WHERE EXISTS (SELECT * FROM Lib_tab x WHERE x.refno = r.refno)
GROUP BY id_no
) c ON m.id_no = c.id_no
[/code]

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

gfaryd
Starting Member

27 Posts

Posted - 2010-07-15 : 07:34:16

thanx khtan

Farid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources