CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
FrenkyB
FrenkyB

Reputation: 7197

Update second table based on values / keys from the first table

I have two tables (one table is created from xml). I have to update columns in the second table, based on values and keys from the first table.

Tables have same columns. First table:

DECLARE @tblORAS_NET TABLE
       (             
             ORAS_KEY INT,
             ORAS_NET DECIMAL(15,4)
       )

Second table is almost the same:

DECLARE @tblORAS_NET1 TABLE
           (             
                 ORAS_KEY INT,
                 ORAS_NET DECIMAL(15,4)
           )

My question is - how to update ORAS_NET in the second table based on ORAS_NET from the first table and ORAS_KEY from the first table? My main problem is that I don't know how to write where condition on the ORAS_KEY and at the same time get ORAS_NET?

declare @xml_ORAS_NET xml = '<DataTable>
<TR><ORAS_KEY>1</ORAS_KEY><ORAS_NET>284.03</ORAS_NET></TR>
<TR><ORAS_KEY>2</ORAS_KEY><ORAS_NET>543.12</ORAS_NET></TR>
<TR><ORAS_KEY>3</ORAS_KEY><ORAS_NET>981.91</ORAS_NET></TR>
</DataTable>';

DECLARE @tblORAS_NET TABLE
       (             
             ORAS_KEY INT,
             ORAS_NET DECIMAL(15,4)
       )

       IF (@xml_ORAS_NET IS NOT NULL)
       BEGIN
             INSERT INTO @tblORAS_NET (ORAS_KEY, ORAS_NET)
        SELECT 
             DataTable.TR.value('ORAS_KEY[1]','INT') AS ORAS_KEY,
             DataTable.TR.value('ORAS_NET[1]','DECIMAL(15,4)') AS ORAS_NET
        FROM @xml_ORAS_NET.nodes('/DataTable/TR') DataTable(TR) 
       END


          select * from @tblORAS_NET


    DECLARE @tblORAS_NET1 TABLE
       (             
             ORAS_KEY INT,
             ORAS_NET DECIMAL(15,4)
       )

       insert into @tblORAS_NET1(oras_key) values(1),(2),(3)

       select * from @tblORAS_NET1

Upvotes: 0

Views: 51

Answers (1)

Brett
Brett

Reputation: 1550

I believe this is the update statement you want - though you haven't stated your expected results so I'm taking a bit of a stab in the dark...

   update net1
   set net1.ORAS_NET = net.ORAS_NET
   from @tblORAS_NET net 
   join @tblORAS_NET1 net1 
     on net.ORAS_KEY = net1.ORAS_KEY

Upvotes: 2

Related Questions