Sql-server – Update table using values from another table in SQL Server

join;sql serversql server 2014t-sqlupdate

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?

Best Answer

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;

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.

Mysql – INSERT/UPDATE in same query from previous SELECT with some singularities

I've made a function where you just put the string and the output will be in Capital Letters:

    DELIMITER //

    CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1
        DETERMINISTIC
    BEGIN

    DECLARE vPOSICION INT DEFAULT 0; 
    DECLARE vTMP VARCHAR(150) DEFAULT ''; 
    DECLARE vRESULTADO VARCHAR(150) DEFAULT '';
    DECLARE vCADENA VARCHAR(150) DEFAULT '';
    IF eCADENA IS NULL THEN
        SET vRESULTADO='';
        RETURN vRESULTADO;
    ELSE
        SET vCADENA=LCASE(eCADENA);
        REPEAT 
            SET vPOSICION=LOCATE(' ', vCADENA); 
            IF vPOSICION=0 THEN 
                SET vPOSICION=CHAR_LENGTH(vCADENA); 
            END IF; 
            SET vTMP=LEFT(vCADENA,vPOSICION); 
            IF CHAR_LENGTH(vTMP) < 4 THEN 
                SET vRESULTADO=CONCAT(vRESULTADO, vTMP); 
            ELSE 
                SET vRESULTADO=CONCAT(vRESULTADO, UPPER(LEFT(vTMP,1)),SUBSTRING(vTMP,2)); 
            END IF; 
            SET vCADENA=RIGHT(vCADENA,CHAR_LENGTH(vCADENA)-vPOSICION); 
        UNTIL CHAR_LENGTH(vCADENA) = 0 
        END REPEAT; 
        RETURN CONCAT(UPPER(LEFT(vRESULTADO,1)),MID(vRESULTADO,2,150)); 
    END IF;
    END

    //
    DELIMITER ;

Example:

    INSERT INTO pdone.reps 
        (veeva_rep_id,display_name,username,`first`,`last`,email) 
    SELECT 
        Id, 
        CONCAT(LCAPITAL(firstname),' ',LCAPITAL(lastname)) AS display_name,
        username, 
        firstname, 
        lastname, 
        email 
    FROM veeva.`user` 
    WHERE Id = "00580000003UB5VAAW";