Sql 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, indi

Solution 1:

First, figure out which records need to be updated:

select *
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
    and i.sect_nbr = g.sect_nbr
where
    g.indicat != i.indicat

Now, modify the query to update those records with the correct grp_id. Notice that I've added an extra join to the tbl_group table with an alias of "g2". This will be the correct group.

update o set
    o.grp_id = g2.grp_id
from tbl_order o
innerjoin tbl_group g on
    g.grp_id = o.grp_id
innerjoin tbl_indicator i on
    i.grp_nbr = g.grp_nbr
    and i.sect_nbr = g.sect_nbr
innerjoin tbl_group g2 on
    g2.grp_nbr = i.grp_nbr
    and g2.sect_nbr = i.sect_nbr
    and g2.indicat = i.indicat
where
    g.indicat != i.indicat

Note that due to the inner join on tbl_group g2, the records will not be updated if there does not exist any tbl_group record where indicat matches the indicat value of its associated tbl_indicator record.