16

I have 2 table in my database.

Table #1

-------------------------------------------------------------------------
| name | family | phone | email | gender | phone2 | address | birthdate |
-------------------------------------------------------------------------

Table #2

-----------------------------------------
| gender | address | phone | birthdate |
-----------------------------------------

in table #1 columns address and phone2 is empty and columns gender and birthdate values is same as table #2.

How can I read data from table #2 and update address and phone2 in table #1 with values from table #2 address and phone columns when gender and birthdate is the same in each row?

for example: this is some data in Table #1

-------------------------------------------------------------------------
| name | family | phone | email | gender | phone2 | address | birthdate |
-------------------------------------------------------------------------
| john | doe    | 12345| t@t.com| Male  |         |         | 1980-01-01|
-------------------------------------------------------------------------
| mike | clark  | 65432| x@y.com| Male  |         |         | 1990-01-01|
-------------------------------------------------------------------------
| Sara | King   | 875465| a@b.com|Female|         |         | 1970-01-01|
-------------------------------------------------------------------------

and here is some data in table #2

-----------------------------------------
| gender | address | phone | birthdate  |
-----------------------------------------
| Male   | 1704test|0457852|1980-01-01  |
-----------------------------------------
| Female | 1705abcs|0986532|1970-01-01  |
-----------------------------------------
| Male   | 1602cyzd|0326589|1990-01-01  |
-----------------------------------------

I want to update table #1 with data from table #2 and check gender and birthdate and make table #1 like

-------------------------------------------------------------------------
| name | family | phone | email | gender | phone2 | address | birthdate |
-------------------------------------------------------------------------
| john | doe    | 12345| t@t.com| Male   |0457852 |1704test | 1980-01-01|
-------------------------------------------------------------------------
| mike | clark  | 65432| x@y.com| Male   |0326589  |1602cyzd| 1990-01-01|
-------------------------------------------------------------------------
| Sara | King   | 875465| a@b.com|Female |0986532  |1705abcs| 1970-01-01|
-------------------------------------------------------------------------

How can I do this?

  • 1
    And what if there are 2 or more people with same gender and birthdate? Which phone and address (of the many) should be copied? – ypercubeᵀᴹ May 18 '16 at 14:54
  • its not possible, this is just test table, in my real data its not possible to same person have same values. – John Doe May 18 '16 at 14:57
  • If it is really not possible, i.e. if there is a UNIQUE constraint on table2 (gender, birthdate), you should add that info in the question. – ypercubeᵀᴹ May 18 '16 at 16:22
27

There are quite a few ways to achieve your desired results.

Undeterministic methods

(in the event that many rows in table 2 match one in table 1)

UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       JOIN #Table2 T2
         ON T1.gender = T2.gender
            AND T1.birthdate = T2.birthdate

Or a slightly more concise form

UPDATE #Table1
SET    address = #Table2.address,
       phone2 = #Table2.phone
FROM   #Table2
WHERE  #Table2.gender = #Table1.gender
       AND #Table2.birthdate = #Table1.birthdate 

Or with a CTE

WITH CTE
     AS (SELECT T1.address AS tgt_address,
                T1.phone2  AS tgt_phone,
                T2.address AS source_address,
                T2.phone   AS source_phone
         FROM   #Table1 T1
                INNER JOIN #Table2 T2
                  ON T1.gender = T2.gender
                     AND T1.birthdate = T2.birthdate)
UPDATE CTE
SET    tgt_address = source_address,
       tgt_phone = source_phone 

Deterministic methods

MERGE would throw an error rather than accept non deterministic results

MERGE #Table1 T1
USING #Table2 T2
ON T1.gender = T2.gender
   AND T1.birthdate = T2.birthdate
WHEN MATCHED THEN
  UPDATE SET address = T2.address,
             phone2 = T2.phone; 

Or you could pick a specific record if there is more than one match

With APPLY

UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       CROSS APPLY (SELECT TOP 1 *
                    FROM   #Table2 T2
                    WHERE  T1.gender = T2.gender
                           AND T1.birthdate = T2.birthdate
                    ORDER  BY T2.PrimaryKey) T2 

.. Or a CTE

WITH T2
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gender, birthdate ORDER BY primarykey) AS RN
         FROM   #Table2)
UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       JOIN T2
         ON T1.gender = T2.gender
            AND T1.birthdate = T2.birthdate
            AND T2.RN = 1;
| improve this answer | |
  • Thanks for your great help! I Have 2 question: 1) I think it's simple way to do this, I think this way is decrease performance and if I have about 50milion record this way is very slow, do you agree? 2) in this way, if I want join 2table and some columns in table #2 does not exist in table #1 I got any error? for example if I have color column in table #2 and its not exist on table #1, join process got error or just join columns exist in 2tables? Thanks again... – John Doe May 18 '16 at 20:29
  • 1
    @JohnDoe if you have a performance question ask a new question and supply details of the table sizes, structures, indexes and execution plans. I don't understand what you are asking in point 2 please edit your question and supply example table structures that demonstrate the issue you are asking about. – Martin Smith May 18 '16 at 20:45
  • 1
    @JohnDoe: If you mean by column you mean a column value (in other words, a matching row) – when there is no matching row, no error is raised. In case of an inner join (like here), the unmatched row(s) just won't be updated.But if you indeed meant a column that exists in one table and doesn't exist in the other, then I believe that's one more new question to ask separately. – Andriy M May 19 '16 at 8:35
  • On that first CTE, how does SQL Server know what table to update? – RonJohn May 24 '19 at 5:56
  • @RonJohn It knows the source of the columns. i.e. that both tgt_address and tgt_phone are aliases for columns in #Table1 - so that is the target for the update. – Martin Smith May 24 '19 at 12:53
0
UPDATE TS
SET TS.TaskFullAddress = L.FullAddress
FROM [dbo].[TaskOrders]   TS
INNER JOIN Locations L
ON  TS.ClientId  = L.ClientId;
| improve this answer | |
  • The field names in the answer don't match the field names in the question, but the technique works. – RonJohn May 24 '19 at 6:05
  • Thanks Ron, just giving the idea – David Fawzy May 24 '19 at 8:45
  • It is, though, why your answer was voted down. – RonJohn May 24 '19 at 13:29
  • Thanks not sure, as i copied code was working in my live application – David Fawzy May 24 '19 at 13:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.