Bdtjtk

I am looking for SQL to update using 3 tables.

For example:
Table 1:

ID   Address  Name       MutipleID

1    ABC      Apple       123xyz

2    DEF      Mango       456stu

3    HFG      Orange      789wac

Table 2:

ID     ADDRESS   NAME      MutipleID

1      ABC       null      123xyz

2      null      Mango     456stu

null   HGF       null      789wac

Table 3:

ID      ADDRESS    NAME     MutlipleID

T                           123xyz

null      T                 456stu

null     null       T       789wac

so, my logic here should be like,

• 'Case 1: Table1.ID = Table2.id --> Update table 3.ID = T Else'


• 'Case 2: Table1.Address = table2.address --> Update table3. address = 'T'
  else'


• 'Case 3: Table1.Name = table2.name --> Update Table3.Name = 'T''

my table3 is mainly a lookup table to check which columns matched from TABLE 1 & 2..

All these 3 tables have one common column "MutilpleID" which is their linking column to make udpates

I'm not sure how to implement this logic.

I see it in this way:

update Table3

set

    ID      = case when t1.ID=t2.ID then 'T' else null end,

    ADDRESS = case when t1.ID<>t2.ID and t1.Address=t2.ADDRESS then 'T' else null end,

    NAME    = case when t1.ID<>t2.ID and t1.Address<>t2.ADDRESS

                    and t1.Name=t2.NAME then 'T' else null end

from Table3 t3

join Table2 t2 on (t3.MultipleID=t2.MultipleID)

join Table1 t1 on (t2.MultipleID=t1.MultipleID)

This can be done within a Data Flow Task by using a Lookup component to match table 1 and table 2. From this point the rows can then redirected based off the values in the ID, Address, and Name columns and table 3 updated accordingly. Of course some updates for your exact table and column names will be needed, but an overview of this process is listed below.

• Create a Data Flow Task with an OLE DB source that selects the necessary columns from table 1.


• Add a Lookup component next and connect the OLE DB source to it. Since the MutipleID column contains text I'd recommend using the No cache option to avoid potential collation/case comparison issues unless you want to match by case. By default the Lookup will fail rows without a corresponding match so you will want to either use the Redirect Rows to no Match Output or Ignore Failure option for handling rows without matching entries (on the General pane of the Lookup editor).


• On the Connection pane of the Lookup use the SQL query option and enter a query that returns only the needed columns from table two. Don't use the table/view option unless you need all of the columns the table contains, as this will add unnecessary columns otherwise.


• On the Columns page, link the MutipleID column from both sources. Next add the columns from table 2 to the data flow by checking the box next to each column and selecting the <add as new column> option in the Lookup Operation field. Give them a descriptive name in the Output Alias field, such as LKUP_ID.


• Next add a Conditional Split in the Data Flow Task and connect this to the Lookup Match Output output. For the first condition add a comparison for the ID column of both tables. Note that if these are null this will cause an error. To avoid this, wrap each column in the REPLACENULL function. If you don't want to match nulls use a different value for the second argument on each column. An example of this is at the end of this post (the example doesn't match nulls). The Conditional Split will direct the rows to only one output, being the first output with a condition that returns true. Going by the order in your question, add a condition for the ID columns, followed by the Address and then Name column last. Give each output a name that matches the condition, i.e. Matching IDs Output for the ID comparison.


• Add three OLE DB Commands and connect each one to one of the outputs you created. In the OLE DB Command editor select the same Connection Manager. On the Component Properties page enter an update command that will update the corresponding column in table 3 based off the match that was done in the Conditional Split. An example of this is below. This example uses the first condition, matching IDs. The question mark is a parameter placeholder. The parameter will be mapped on the Column Mappings page. On this page, drag/link the MutlipleID column from the "Available Input Columns" box to the parameter (likely label Param_0) in the "Available Destination Columns" box. This will use MutlipleID column from the data flow to match to the corresponding row in table 3. Update the other OLE DB Command connected to the matching address output to make the same update to the Address column and repeat the same step for the Name output and column as well.


• The OLE DB Commands do operate on a row-by-row basis, so if you're working with a large volume of data I'd suggest replacing these with staging tables and doing the update as a set based update via a join to table 3. For example replacing the OLE DB Commands with staging tables as OLE DB Destinations and doing a set-based update via a T-SQL join in an Execute SQL Task after the Data Flow Task.

Conditional Split Example Condition:

REPLACENULL(ID, 0) == REPLACENULL(LKUP_ID, 1)

OLE DB Command Update SQL:

UPDATE YourSchema.Table3 SET ID = 'T' WHERE MultipleID = ?