tsql update (help me)

i have 3 tables

tbl_indicator
grp_nbr   sect_nbr   indicat
1             100          p
2             101          s


tbl_group
grp_id    grp_nbr    sect_nbr     indicat
333         1              100           a
555         1              100           p
444         2              101           s
222         2              101           y

here (in tbl_group) grp_id is P.K

tbl_order
order_id       grp_id
5000              333
5001              555
5002              555
5003              555
5004              444
5005              444
5006              222
here (in tbl_order) grp_id is F.K to grp_id in tbl_group


now the thing is that in table tbl_indiactor for one set of grp_nbr and sect_nbr there is an indicat, for the same set of grp_nbr and sect_nbr there is a correct indicat(555,1, 100, p) and a junk indicat(333, 1, 100, a) in table tbl_group.
but both these grp_id s(333, 555) are present in table tbl_orders
now i need to update tbl_order table in such a way that the junk grp_id s should replace with correct grp_id s

the output should like

tbl_orders
order_id       grp_id
5000             555
5001              555
5002              555
5003              555
5004              444
5005              444
5006             444

can some one help me please.....

I had to make a few assumptions here, but it looks like what you need to do is create an association table that gives you a "map" of your bad "grp_id"s and maps them to their corresponding good "grp_id"s.   I am assuming that this is to be based on a matching "grp_nbr".

I used CTE here to come up with a solution.  If you run it - you will get an error ( the first time ) because the temp tables do not yet exist - but it will have done it's work in the results window.  If you run it subsequently you should get the desired output without errors.

DROP TABLE #indicator
go
DROP TABLE #group
go
DROP TABLE #order
go

SELECT 1 grp_nbr , 100 sect_nbr, 'p' indicat
  INTO #indicator
UNION
SELECT 2 , 101 , 's'
go

SELECT 333 grp_id , 1 grp_nbr , 100 sect_nbr , 'a' indicat
  INTO #group
UNION
SELECT 555 , 1 , 100 , 'p' 
UNION
SELECT 444 , 2 , 101 , 's' 
UNION
SELECT 222 , 2 , 101 , 'y' 
go

SELECT 5000 order_id , 333 grp_id
  INTO #order
UNION 
SELECT 5001 , 555
UNION 
SELECT 5002 , 555
UNION 
SELECT 5003 , 555
UNION 
SELECT 5004 , 444
UNION 
SELECT 5005 , 444
UNION 
SELECT 5006 , 222
go

--SELECT * 
--  FROM #indicator 
--go
--SELECT * 
--  FROM #group 
--go
PRINT 'before..'
go

SELECT * 
  FROM #order 
go

/* ---------------------------------------------------------------------
   now the thing is that in table tbl_indiactor for one set of 
   grp_nbr and sect_nbr there is an indicat, 
   for the same set of grp_nbr and sect_nbr 
   there is a correct indicat(555, 1, 100, p) 
        and a junk    indicat(333, 1, 100, a) 
   in table tbl_group.  
   
   but both these grp_id s(333, 555) are present in table tbl_orders
   
   now i need to update tbl_order table in such a way that the 
   junk grp_id s should replace with correct grp_id s
--------------------------------------------------------------------- */
WITH GoodGroupIDs AS ( -- this gives a select of only valid group ID / numbers
SELECT g.grp_id
     , g.grp_nbr
  FROM #indicator   i
  JOIN #group       g
    ON i.grp_nbr  = g.grp_nbr
   AND i.sect_nbr = g.sect_nbr 
   AND i.indicat  = g.indicat )
, NeedUpdates AS ( -- this gives a select of only invalid group ID / numbers
SELECT DISTINCT 
       g.grp_id
     , g.grp_nbr 
  FROM #indicator   i
  FULL OUTER
  JOIN #group       g
    ON i.grp_nbr  = g.grp_nbr
   AND i.sect_nbr = g.sect_nbr 
   AND i.indicat  = g.indicat 
 WHERE i.grp_nbr IS NULL
   AND g.grp_nbr IS NOT NULL )
, Fixes AS ( -- This creates a "map" of fixing them based on matching grp_nbr
SELECT g.grp_id Good_ID
     , n.grp_id Bad_ID
     , g.grp_nbr 
  FROM GoodGroupIDs   g
  JOIN NeedUpdates    n
    ON g.grp_nbr    = n.grp_nbr )

UPDATE #order 
   SET grp_id     = fix.Good_ID
  FROM #order       ord
  JOIN Fixes        fix
    ON ord.grp_id = fix.Bad_ID 
go

PRINT 'after..'
go

SELECT order_id
     , grp_id 
  FROM #order
go

Hope that helps / give you what you're after -

Keith Hyer

Assuming the tbl_order table is not meant to be a slowly changing dimension or reflect historical records by any means...a simple update statement like below will do what you are trying.

create table #tbl_indicator (grp_nbr tinyint,  sect_nbr int,  indicat varchar(1))
insert into #tbl_indicator
select 1,100,'p' union
select 2,101,'s'

create table #tbl_group (grp_id int,   grp_nbr tinyint,  sect_nbr int,  indicat varchar(1))
insert into #tbl_group
select 333, 1,             100,           'a'union
select 555,         1  ,            100,           'p' union
select 444 ,        2 ,             101 ,          's' union
select 222  ,       2,              101  ,         'y'

create table #tbl_order (order_id int, grp_id int)
insert into #tbl_order
select 5000              ,333 union
select 5001              ,555 union
select 5002              ,555 union
select 5003              ,555 union
select 5004              ,444 union
select 5005              ,444 union
select 5006              ,222

select * from #tbl_order

update #tbl_order
set grp_id = g2.grp_id
from #tbl_order o
inner join #tbl_group g on g.grp_id = o.grp_id
inner join #tbl_indicator i on i.grp_nbr = g.grp_nbr
inner join #tbl_group g2 on g2.grp_nbr = i.grp_nbr and g2.sect_nbr = i.sect_nbr
where g.indicat <> i.indicat
and g2.grp_id <> o.grp_id

select * from #tbl_order

drop table #tbl_indicator
drop table #tbl_group
drop table #tbl_order

Before:
order_id grp_id
5000 333
5001 555
5002 555
5003 555
5004 444
5005 444
5006 222

After:
order_id grp_id
5000 555
5001 555
5002 555
5003 555
5004 444
5005 444
5006 444