How to use JOIN in UPDATE query

Question

I am very weak at join and i need to know that how can i convert following query using joins. This Query is working fine with sub queries but i need to convert it and use it making joins.

update dbo.TFIN_Stmt_Line_Item_Dtl 
set Stmt_Line_Item_Dtl_Desc='Changes from Baseline' 
where Stmt_Line_Item_Dtl_Desc='Increased Hours' 
and Stmt_Line_Item_Id 
in (select tmt_Line_Item_Ref_Id 
    from dbo.TFIN_Stmt_Line_Item 
    where Stmt_Line_Item_Type_Id =
        (select Stmt_Line_Item_Type_Id  
         from dbo.TFIN_Stmt_Line_Item_Type 
         where Stmt_Line_Item_Desc like 'ABOX/RBOX Amendment 18 and 20'))

Please if any body can help me.

Thanks

@Daniel: This is my fault, wrong layout used. I edited the topic already, please delete your comments — abatishchev
– abatishchev, Commented Dec 2, 2010 at 9:49
whats weired about the heading. i just wanted that how can i convert this query using Joins — Sumit Gupta
– Sumit Gupta, Commented Dec 2, 2010 at 9:49
Why do you need to convert it? Is it broken? Running too slow? Homework? — Marcelo Cantos
– Marcelo Cantos, Commented Dec 2, 2010 at 9:49
yup as i m having lacs of records in each of the table and hence this is running very slow. can i have it using Joins — Sumit Gupta
– Sumit Gupta, Commented Dec 2, 2010 at 9:50

Lieven Keersmaekers · Accepted Answer · 2010-12-02 09:57:31Z

3

Following query uses joins to update your table but if it would be faster remains to be seen.

Could you post the query plan so we can have a look at that

update  dbo.TFIN_Stmt_Line_Item_Dtl 
set     Stmt_Line_Item_Dtl_Desc = 'Changes from Baseline' 
from    dbo.TFIN_Stmt_Line_Item_Dtl dtl
        inner join dbo.TFIN_Stmt_Line_Item li on li.tmt_Line_Item_Ref_Id = dtl.Stmt_Line_Item_Id
        inner join dbo.TFIN_Stmt_Line_Item_Type lit on lit.Stmt_Line_Item_Type_Id = li.Stmt_Line_Item_Type_Id
where   dtl.Stmt_Line_Item_Dtl_Desc='Increased Hours' 
        and lit.Stmt_Line_Item_Desc like 'ABOX/RBOX Amendment 18 and 20'

answered Dec 2, 2010 at 9:57

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

abatishchev · Accepted Answer · 2010-12-02 10:06:37Z

update dbo.TFIN_Stmt_Line_Item_Dtl 
set Stmt_Line_Item_Dtl_Desc='Changes from Baseline' 
where Stmt_Line_Item_Dtl_Desc='Increased Hours' 
and Stmt_Line_Item_Id 
in (select tmt_Line_Item_Ref_Id 
    from dbo.TFIN_Stmt_Line_Item Item
    JOIN dbo.TFIN_Stmt_Line_Item_Type Type
    on Type.Stmt_Line_Item_Type_Id = Item.Stmt_Line_Item_Type_Id
    and Type.Stmt_Line_Item_Desc like 'ABOX/RBOX Amendment 18 and 20')

or

update dbo.TFIN_Stmt_Line_Item_Dtl 
set Stmt_Line_Item_Dtl_Desc = 'Changes from Baseline' 
from dbo.TFIN_Stmt_Line_Item_Dtl Detail
JOIN dbo.TFIN_Stmt_Line_Item Item
on Item.tmt_Line_Item_Ref_Id = Detail.Stmt_Line_Item_Id
JOIN dbo.TFIN_Stmt_Line_Item_Type Type
on Type.Stmt_Line_Item_Type_Id = Item.Stmt_Line_Item_Type_Id
and Type.Stmt_Line_Item_Desc like 'ABOX/RBOX Amendment 18 and 20')
where Detail.Stmt_Line_Item_Dtl_Desc = 'Increased Hours'

Collectives™ on Stack Overflow

How to use JOIN in UPDATE query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest