Tuesday, July 16, 2013

Scripts for Self updated Table using multiple Join

Here we have described how to update a table using multiple joins on table

1. Create a Table Variable:

Declare @tblR as Table
(
    RID int,
    SID uniqueidentifier,
    BID uniqueidentifier
)

2. Insert demo data into New Created Table Variable





insert into @tblR values (1, null, null)
insert into @tblR values (2, null, null)
insert into @tblR values (3, null, null)
insert into @tblR values (4, null, null)
insert into @tblR values (5, null, null)
insert into @tblR values (1, null, null)
insert into @tblR values (2, null, null)
insert into @tblR values (6, null, null)
insert into @tblR values (7, null, null)
insert into @tblR values (8, null, null)

3.  Update Table using alias name and Using multiple INNER JOIN





update t set t.BID = b.BID, t.ID = s.ID
from @tblR t
inner join R r on r.ID = t.RID
inner join B b on b.BID = r.BID
inner join S s on s.SID = b.SID

4. Select data from table Variable to check updated data






select * from @tblR

This is just an example for a simple update using inner join.

Happy Living....
Happy Coding.....
Happy Concepts.....




No comments:

Post a Comment