update query help
Jami
ear All
i have following DDL and sample data
[sql 2000]
create table main_tab (t_id varchar(10),fname varchar(30), lname
varchar(30), gender char(1),status smallint)
create table im_tab (t_id varchar(10),flag char(1))
insert into main_tab values('a0101','Ali','Hussain','M',null)
insert into main_tab values('a0102','Noor','Inam',null)
insert into main_tab values('a0103','Siddique','Hussain','M',null)
insert into main_tab values('a0104','Farooq','Ali','M',null)
insert into main_tab values('a0105','Ahmed','Mushtaq','M',null)
insert into main_tab values('a0106','Qurban','Khan','M',null)
insert into main_tab values('a0107','Ahmed','Hasan','M',null)
insert into im_tab values('a0101','a')
insert into im_tab values('a0101','1')
insert into im_tab values('a0101','2')
insert into im_tab values('a0101','3')
insert into im_tab values('a0102','a')
insert into im_tab values('a0102','1')
insert into im_tab values('a0102','2')
insert into im_tab values('a0102','3')
insert into im_tab values('a0103','a')
insert into im_tab values('a0103','1')
insert into im_tab values('a0103','2')
insert into im_tab values('a0104','a')
insert into im_tab values('a0105','1')
insert into im_tab values('a0105','2')
insert into im_tab values('a0105','3')
i want to set status = 1 where records from main_tab have complete set
of data
in im_table,
like a0101 and a0102 forms complete set in im_table
how can i do that
*** Sent via Developersdex http://www.developersdex.com ***
Erland Sommarskog
> i want to set status = 1 where records from main_tab have complete set
> of data in im_table,
> like a0101 and a0102 forms complete set in im_table
for instance 'b', 5, 6 and 7 also could be a complete set. This query
assumes that 'a', 1, 2, 3 is the only possibility:
UPDATE main_tab
SET status = 1
FROM main_tab m
WHERE (SELECT COUNT(*)
FROM im_tab i
JOIN (SELECT flag = 'a' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' ) AS u ON i.flag = u.flag
WHERE m.t_id = i.t_id) = 4
go
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
