Prev: sql server conflicts between versions?
Next: varbinary conversion
From: Jami on 14 Jul 2010 23:07


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



*** Sent via Developersdex http://www.developersdex.com ***
From: --CELKO-- on 15 Jul 2010 13:17
Forget about creating a table for the counts; do it in a VIEW that
will always be correct. You can also use the new table constructor
syntax to load data into tables.

CREATE TABLE Rec -- not a table at all!
(vague_nbr VARCHAR(5),
ref_nbr SMALLINT));

INSERT INTO Rec -- not a table at all!
VALUES(1, 1), (1, 1), (1, 2), (1, 2), (1, 2), (1, 3),
(2, 1), (2, 1),
(3, 2), (3, 2), (3, 2), (3, 3),
(4, 3), (4, 3), (4, 2), (4, 1), (4, 1);

CREATE TABLE Lib
(ref_nbr SMALLINT NOT NULL PRIMARY KEY);

INSERT INTO Lib VALUES(1), (2), (3), (4), (5);


CREATE VIEW RecCounts
AS
SELECT L.ref_nbr, COUNT(DISTINCT R.ref_nbr) AS distinct_ref_cnt
FROM Lib AS L, Rec AS R
WHERE L.vague_nbr = R.vague_nbr
GROUP BY L.ref_nbr;

You need to clean up the actual tables so you will have keys.
 | 
Pages: 1
Prev: sql server conflicts between versions?
Next: varbinary conversion