Update one table's row from the results of multiple rows of another table

Question

I've seen posts on how to update multiple rows (specifically using case?), but my question is the inverse.

I have a table of phone_numbers

Person_ID	Phone_number
1	102-345-6789
1	102-445-6789
2	102-545-6789

And a table of contacts:

peopleid	Phone_number_1	Phone_number_2	Name
1			Joe
2			Bob

I'd like to get the phone_numbers into the one row of the contacts table.

And all I have is a hot mess of attempts, none of which have worked, mostly because I'm doing joins? that are not explicit? so the multi-part identifiers don't work.

I wanted to make sure I was getting each row (and not just the first row of what the join decides), so I did this:

DECLARE @id int
SELECT @id = min(e1.Person_ID)
FROM out_1E e1
WHILE @id IS NOT NULL
BEGIN
  -- other stuff below


  select @id = min( Person_ID )
  from out_1E
  where Person_ID > @id
END

And then my attempt at the update statement inside, where I want it to update the first column if null, and if not, the second column (I only care about the first two).

DECLARE @phone NVARCHAR
  SELECT @phone = contacts.Phone_number_1 -- THIS DOESN'T WORK
  IF @phone IS NULL

UPDATE contacts
SET [Phone_number_1] = isnull([Phone_number_1], e1.Phone_number)
FROM out_1E e1
WHERE contacts.peopleid = e1.Person_ID

In total:

DECLARE @id int
SELECT @id = min(e1.Person_ID)
FROM out_1E e1
WHILE @id IS NOT NULL
BEGIN
  DECLARE @phone NVARCHAR
  SELECT @phone = contacts.Phone_number_1
  IF @phone IS NULL

  
   UPDATE contacts
SET [Phone_number_1] = isnull( [Phone_number_1], e1.Phone_Number)
FROM out_1E e1
WHERE contacts.peopleid = e1.Person_ID


  select @id = min( Person_ID )
  from out_1E
  where Person_ID > @id
END

These are not the column names, so if there are typos there, it's only on this post.

How would I go about correcting this so I iterate through each row of the phone_numbers table to populate the contacts table with phone numbers?

Answer 1

I don't think this is an elegant solution, but I just split it up and added a WHERE clause on the second iteration.

UPDATE contacts
SET [Home Phone] = ISNULL([Home Phone], e1.Phone_Number)
FROM out_1E e1
WHERE contacts.peopleid = e1.Person_ID
AND [Person_Phone_or_email_Type] = 'HOME'

UPDATE contacts
SET [Home Phone 2] = ISNULL([Home Phone 2], e1.Phone_Number)
FROM contacts
INNER JOIN out_1E e1
ON contacts.peopleid = e1.Person_ID
WHERE [Home Phone] <> e1.Phone_Number
AND Person_Phone_or_email_Type = 'HOME'

I'm sure there's a better way of doing this, to be more inclusive, but this does appear to work.

Answer 2

For your exact scenario, I usually just implement some kind of PIVOT logic in my code. Link to MSDN documentation here - https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

To set up your tables as you have shown them, I've ran this code -

CREATE TABLE #phone_numbers
(
    id INT
  , phone_number VARCHAR(12)
);

CREATE TABLE #contacts
(
    id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
  , phone_number_1 VARCHAR(12)
  , phone_number_2 VARCHAR(12)
  , name VARCHAR(10)
);

INSERT INTO #contacts
VALUES
('', '', 'Joe')
, ('', '', 'Bob')
, ('', '', 'Steve');

INSERT INTO #phone_numbers
VALUES
(1, '102-345-6789')
, (1, '102-445-6789')
, (2, '102-545-6789')
, (2, '102-645-6789')
, (3, '102-845-6789');

Simply running a SELECT on those tables give me this -

id          phone_number
----------- ------------
1           102-345-6789
1           102-445-6789
2           102-545-6789
2           102-645-6789
3           102-845-6789

id          phone_number_1 phone_number_2 name
----------- -------------- -------------- ----------
1                                         Joe
2                                         Bob
3                                         Steve

Since your phone_number table doesn't really contain an order of phone numbers, I've just had it assume that there will be a maximum of two phone numbers in the phone_number table, and if there are more, those will just be ignored.

Running my select with my pivot below -

SELECT c.id
     , c.phone_number_1
     , c.phone_number_2
     , c.name
     , pn_display.[1]
     , pn_display.[2]
FROM #contacts AS c
    CROSS APPLY
(
    SELECT pn_pvt.[1]
         , pn_pvt.[2]
    FROM
    (
        SELECT pn.phone_number
             , ROW_NUMBER() OVER (PARTITION BY pn.id ORDER BY pn.id) AS rn
        FROM #phone_numbers AS pn
        WHERE c.id = pn.id
    ) AS pn_order
    PIVOT
    (
        MAX(phone_number)
        FOR rn IN ([1], [2])
    ) pn_pvt
) AS pn_display;

Yields this table -

id          phone_number_1 phone_number_2 name       1            2
----------- -------------- -------------- ---------- ------------ ------------
1                                         Joe        102-345-6789 102-445-6789
2                                         Bob        102-545-6789 102-645-6789
3                                         Steve      102-845-6789 

According to your specific request -

And then my attempt at the update statement inside, where I want it to update the first column if null, and if not, the second column (I only care about the first two).

The update statement would look like this -

UPDATE c
SET c.phone_number_1 = IIF(ISNULL(c.phone_number_1, '') = '', [1], c.phone_number_1)
  , c.phone_number_2 = IIF(ISNULL(c.phone_number_1, '') != '', [2], c.phone_number_2)
FROM #contacts AS c
    CROSS APPLY
(
    SELECT [1]
         , [2]
    FROM
    (
        SELECT pn.phone_number
             , ROW_NUMBER() OVER (PARTITION BY pn.id ORDER BY pn.id) AS rn
        FROM #phone_numbers AS pn
        WHERE c.id = pn.id
    ) AS pn_order
    PIVOT
    (
        MAX(phone_number)
        FOR rn IN ([1], [2])
    ) pn_pvt
) AS pn_display
WHERE (
          ISNULL(c.phone_number_1, '') = ''
          AND ISNULL([1], '') != ''
      )
      OR
      (
          ISNULL(c.phone_number_2, '') = ''
          AND ISNULL([2], '') != ''
      );

This statement would select only records where 1 or both of the 2 phone_number columns in the contacts table were null or empty yet 1 or 2 records in the phone_numbers table were available for that contact. If the phone_number_1 field were empty, it would populate only that field. If it wasn't empty, but the phone_number_2 field was, it would fill in the second number if there were a second entry for that specific id in the phone_numbers table. That ensures that even if there are no records to update, rows are not updated with existing data. Depending on the size of the table that could be a nasty performance hit.

Design choices aside, that's how you would get the following over 2 executions (The first for phone number 1, the second for phone number 2). If you executed the update three times, the first would update 3 rows, the second would update 2, and the third would update 0.

SELECT c.id
     , c.phone_number_1
     , c.phone_number_2
     , c.name
FROM #contacts AS c;

id          phone_number_1 phone_number_2 name
----------- -------------- -------------- ----------
1           102-345-6789   102-445-6789   Joe
2           102-545-6789   102-645-6789   Bob
3           102-845-6789                  Steve