What would be the best way to conditionally insert or update a table that has this unique design?

Question

I have an items table that has columns for person ID, position #, and individual lists of items that really aren't associated with the other fields in the record. I have no control over the structure of the table, so I cannot change the design of it.

Here's an example of what it looks like (T-SQL at the end of my question):

PersonId    Pos         FavoriteColors  AllGifts        BirthDayGifts   HolidayGifts
----------- ----------- --------------- ------------------------------- -------------
1234        1           Blue            Mug             Mug             Computer
1234        2           Green           Phone           Candle          Phone
1234        3           Purple          Candle          Boardgame       NULL
1234        4           Yellow          Computer        Basketball      NULL
1234        5           Orange          Boardgame       NULL            NULL
1234        6           Red             Basketball      NULL            NULL
1234        7           Gray            NULL            NULL            NULL
1234        8           Brown           NULL            NULL            NULL
2222        1           Yellow          NULL            NULL            NULL
4118        1           Pink            Clothes         Handsoap        Clothes
4118        2           Green           Handsoap        NULL            NULL

I realize this is not great table design. In any case, the situation is that every person gets a Book for a holiday gift. Also, there's a new person (4442) who also received a book. I have a two column table that represents this.

Person, gift

1234, Book

2222, Book

4118, Book

4442, Book

Based on how the destination table works, the HolidayGifts and AllGifts columns need have "Book" added as a new value for each person. This may require an INSERT, UPDATE, or both...

The resulting table should look like this:

PersonId    Pos         FavoriteColors  AllGifts        BirthDayGifts   HolidayGifts
----------- ----------- --------------- ------------------------------- -------------
1234        1           Blue            Mug             Mug             Computer
1234        2           Green           Phone           Candle          Phone
1234        3           Purple          Candle          Boardgame       Book
1234        4           Yellow          Computer        Basketball      NULL
1234        5           Orange          Boardgame       NULL            NULL
1234        6           Red             Basketball      NULL            NULL
1234        7           Gray            Book            NULL            NULL
1234        8           Brown           NULL            NULL            NULL
2222        1           Yellow          Book            NULL            Book
4118        1           Pink            Clothes         Handsoap        Clothes
4118        2           Green           Handsoap        NULL            Book
4118        3           NULL            Book            NULL            NULL
4442        1           NULL            Book            NULL            Book

In the case of person 1234, this means:

an Update statement to the HolidayGifts column where PersonId = 1234 and Pos = 3 (the pos of the first null value for the HolidayGifts column)
an Update statement to the AllGifts column where PersonId = 1234 and Pos = 7 (the pos of the first null value for the AllGifts column)

In the case of person 2222, this means:

same as 1234--update the first null value for the HolidayGifts and AllGifts columns. They both happen to be pos = 1, so in this particular case, it could be done in one statement where PersonId = 2222 and pos = 1...

In the case of person 4118:

an update needs to happen to the HolidayGifts column where PersonId = 4118 and Pos = 2 (the first null value of the HolidayGifts column)

AND

an insert needs to happen (with a personid = 4118, pos = 3 and AllGifts = "Book") because AllGifts doesn't have any NULL values at the end for this person.

In the case of person 4442:

They're brand new, so they're a simple insert with values for PersonId, POS, HolidayGifts, and AllGifts.

I hope that all makes sense.

I realize the way this works is a bit awkward, but I have to imagine there's some way to accomplish this in a set-based way.

Can anyone help me with this?

Also, here's the T-SQL to create the before and after tables.

Thank you in advance!

Mike

DECLARE @ItemsBefore AS TABLE 
  ( 
     PersonId       INT NULL, 
     Pos            INT NULL, 
     FavoriteColors VARCHAR(100) NULL, 
     AllGifts       VARCHAR(100) NULL, 
     BirthDayGifts  VARCHAR(100) NULL, 
     HolidayGifts   VARCHAR(100) NULL 
  ) 

INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 1, N'Blue', N'Mug', N'Mug', N'Computer')
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 2, N'Green', N'Phone', N'Candle', N'Phone')
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 3, N'Purple', N'Candle', N'Boardgame', NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 4, N'Yellow', N'Computer', N'Basketball', NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 5, N'Orange', N'Boardgame', NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 6, N'Red', N'Basketball', NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 7, N'Gray', NULL, NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 8, N'Brown', NULL, NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 1, N'Pink', N'Clothes', N'Handsoap', N'Clothes')
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 2, N'Green', N'Handsoap', NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (2222, 1, N'Yellow', NULL, NULL, NULL)

SELECT *
FROM   @ItemsBefore
ORDER  BY PersonId, Pos

DECLARE @ItemsAfter AS TABLE 
  ( 
     PersonId       INT NULL, 
     Pos            INT NULL, 
     FavoriteColors VARCHAR(100) NULL, 
     AllGifts       VARCHAR(100) NULL, 
     BirthDayGifts  VARCHAR(100) NULL, 
     HolidayGifts   VARCHAR(100) NULL 
  ) 

INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 1, N'Blue', N'Mug', N'Mug', N'Computer')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 2, N'Green', N'Phone', N'Candle', N'Phone')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 3, N'Purple', N'Candle', N'Boardgame', N'Book')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 4, N'Yellow', N'Computer', N'Basketball', NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 5, N'Orange', N'Boardgame', NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 6, N'Red', N'Basketball', NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 7, N'Gray', N'Book', NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 8, N'Brown', NULL, NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 1, N'Pink', N'Clothes', N'Handsoap', N'Clothes')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 2, N'Green', N'Handsoap', NULL, N'Book')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 3, NULL, N'Book', NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (2222, 1, N'Yellow', N'Book', NULL, N'Book')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4442, 1, NULL, N'Book', NULL, N'Book')

SELECT *
FROM   @ItemsAfter
ORDER  BY PersonId, Pos

Hannah Vernon · Accepted Answer · 2020-11-23 15:27:02Z

I believe the solutions is shown in my code below, however it is very detailed and hence complex. Maybe you'll simplify it (if you find it correct, of course).

See fiddle

The details from the fiddle are below. First we'll create the table, and add some data to it:

-- Source table

CREATE TABLE ItemsBefore
  ( 
     PersonId       INT NULL, 
     Pos            INT NULL, 
     FavoriteColors VARCHAR(100) NULL, 
     AllGifts       VARCHAR(100) NULL, 
     BirthDayGifts  VARCHAR(100) NULL, 
     HolidayGifts   VARCHAR(100) NULL 
  );

INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 1, N'Blue', N'Mug', N'Mug', N'Computer');
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 2, N'Green', N'Phone', N'Candle', N'Phone');
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 3, N'Purple', N'Candle', N'Boardgame', NULL);
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 4, N'Yellow', N'Computer', N'Basketball', NULL);
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 5, N'Orange', N'Boardgame', NULL, NULL);
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 6, N'Red', N'Basketball', NULL, NULL);
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 7, N'Gray', NULL, NULL, NULL);
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 8, N'Brown', NULL, NULL, NULL);
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 1, N'Pink', N'Clothes', N'Handsoap', N'Clothes');
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 2, N'Green', N'Handsoap', NULL, NULL);
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (2222, 1, N'Yellow', NULL, NULL, NULL);

-- Add a row with PersonId which is not mentioned in update data
INSERT ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4000, 1, N'Pink', N'Clothes', N'Handsoap', NULL);

SELECT *
FROM   ItemsBefore
ORDER  BY PersonId, Pos;

╔═══════════╦══════╦═════════════════╦═════════════╦════════════════╦══════════════╗
║ PersonId  ║ Pos  ║ FavoriteColors  ║  AllGifts   ║ BirthDayGifts  ║ HolidayGifts ║
╠═══════════╬══════╬═════════════════╬═════════════╬════════════════╬══════════════╣
║     1234  ║   1  ║ Blue            ║ Mug         ║ Mug            ║ Computer     ║
║     1234  ║   2  ║ Green           ║ Phone       ║ Candle         ║ Phone        ║
║     1234  ║   3  ║ Purple          ║ Candle      ║ Boardgame      ║              ║
║     1234  ║   4  ║ Yellow          ║ Computer    ║ Basketball     ║              ║
║     1234  ║   5  ║ Orange          ║ Boardgame   ║                ║              ║
║     1234  ║   6  ║ Red             ║ Basketball  ║                ║              ║
║     1234  ║   7  ║ Gray            ║             ║                ║              ║
║     1234  ║   8  ║ Brown           ║             ║                ║              ║
║     2222  ║   1  ║ Yellow          ║             ║                ║              ║
║     4000  ║   1  ║ Pink            ║ Clothes     ║ Handsoap       ║              ║
╚═══════════╩══════╩═════════════════╩═════════════╩════════════════╩══════════════╝

-- The table with the data for updating
-- Pay attention - PersonId must be unique

CREATE TABLE updates (PersonId INT UNIQUE, gift VARCHAR(100));
INSERT INTO updates VALUES
(1234, 'Book'),
(2222, 'Book'),
(4118, 'Book'),
(4442, 'Book');
SELECT * FROM updates;

╔═══════════╦══════╗
║ PersonId  ║ gift ║
╠═══════════╬══════╣
║     1234  ║ Book ║
║     2222  ║ Book ║
║     4118  ║ Book ║
║     4442  ║ Book ║
╚═══════════╩══════╝

-- Intermediate demo
-- We mark existing rows which must and may be updated
-- Also we calculate next Pos value (if insertion occures to be needed)

SELECT *, 
       CASE WHEN LAG(AllGifts) OVER (PARTITION BY PersonId ORDER BY Pos) IS NOT NULL AND AllGifts IS NULL THEN 1 ELSE 0 END update_AllGifts, 
       CASE WHEN LAG(HolidayGifts) OVER (PARTITION BY PersonId ORDER BY Pos) IS NOT NULL AND HolidayGifts IS NULL THEN 1 ELSE 0 END update_HolidayGifts,
       MAX(Pos) OVER (PARTITION BY PersonId) NextPos
FROM ItemsBefore

╔═══════════╦══════╦═════════════════╦═════════════╦════════════════╦═══════════════╦══════════════════╦══════════════════════╦═════════╗
║ PersonId  ║ Pos  ║ FavoriteColors  ║  AllGifts   ║ BirthDayGifts  ║ HolidayGifts  ║ update_AllGifts  ║ update_HolidayGifts  ║ NextPos ║
╠═══════════╬══════╬═════════════════╬═════════════╬════════════════╬═══════════════╬══════════════════╬══════════════════════╬═════════╣
║     1234  ║   1  ║ Blue            ║ Mug         ║ Mug            ║ Computer      ║               0  ║                   0  ║       8 ║
║     1234  ║   2  ║ Green           ║ Phone       ║ Candle         ║ Phone         ║               0  ║                   0  ║       8 ║
║     1234  ║   3  ║ Purple          ║ Candle      ║ Boardgame      ║               ║               0  ║                   1  ║       8 ║
║     1234  ║   4  ║ Yellow          ║ Computer    ║ Basketball     ║               ║               0  ║                   0  ║       8 ║
║     1234  ║   5  ║ Orange          ║ Boardgame   ║                ║               ║               0  ║                   0  ║       8 ║
║     1234  ║   6  ║ Red             ║ Basketball  ║                ║               ║               0  ║                   0  ║       8 ║
║     1234  ║   7  ║ Gray            ║             ║                ║               ║               1  ║                   0  ║       8 ║
║     1234  ║   8  ║ Brown           ║             ║                ║               ║               0  ║                   0  ║       8 ║
║     2222  ║   1  ║ Yellow          ║             ║                ║               ║               0  ║                   0  ║       1 ║
║     4000  ║   1  ║ Pink            ║ Clothes     ║ Handsoap       ║               ║               0  ║                   0  ║       1 ║
║     4118  ║   1  ║ Pink            ║ Clothes     ║ Handsoap       ║ Clothes       ║               0  ║                   0  ║       2 ║
║     4118  ║   2  ║ Green           ║ Handsoap    ║                ║               ║               0  ║                   1  ║       2 ║
╚═══════════╩══════╩═════════════════╩═════════════╩════════════════╩═══════════════╩══════════════════╩══════════════════════╩═════════╝

Using the previous query as a CTE, insert rows where needed:

WITH 
cte1 AS (
SELECT ItemsBefore.*, 
       CASE WHEN LAG(AllGifts) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NOT NULL AND AllGifts IS NULL THEN 1 ELSE 0 END update_AllGifts, 
       CASE WHEN LAG(HolidayGifts) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NOT NULL AND HolidayGifts IS NULL THEN 1 ELSE 0 END update_HolidayGifts,
       MAX(Pos) OVER (PARTITION BY ItemsBefore.PersonId) + 1 NextPos
FROM ItemsBefore
JOIN updates ON ItemsBefore.PersonId = updates.PersonId
),
cte2 AS (
SELECT PersonId,
       NextPos,
       SUM(update_AllGifts) OVER (PARTITION BY PersonId) + SUM(update_HolidayGifts) OVER (PARTITION BY PersonId) needs_insert
FROM cte1
)
INSERT INTO ItemsBefore (PersonId, Pos)
SELECT DISTINCT
       PersonId, NextPos
FROM cte2
WHERE needs_insert = 1;

-- Now the table looks like

SELECT *
FROM ItemsBefore
ORDER BY PersonId, Pos;

╔═══════════╦══════╦═════════════════╦═════════════╦════════════════╦══════════════╗
║ PersonId  ║ Pos  ║ FavoriteColors  ║  AllGifts   ║ BirthDayGifts  ║ HolidayGifts ║
╠═══════════╬══════╬═════════════════╬═════════════╬════════════════╬══════════════╣
║     1234  ║   1  ║ Blue            ║ Mug         ║ Mug            ║ Computer     ║
║     1234  ║   2  ║ Green           ║ Phone       ║ Candle         ║ Phone        ║
║     1234  ║   3  ║ Purple          ║ Candle      ║ Boardgame      ║              ║
║     1234  ║   4  ║ Yellow          ║ Computer    ║ Basketball     ║              ║
║     1234  ║   5  ║ Orange          ║ Boardgame   ║                ║              ║
║     1234  ║   6  ║ Red             ║ Basketball  ║                ║              ║
║     1234  ║   7  ║ Gray            ║             ║                ║              ║
║     1234  ║   8  ║ Brown           ║             ║                ║              ║
║     2222  ║   1  ║ Yellow          ║             ║                ║              ║
║     4000  ║   1  ║ Pink            ║ Clothes     ║ Handsoap       ║              ║
║     4118  ║   1  ║ Pink            ║ Clothes     ║ Handsoap       ║ Clothes      ║
║     4118  ║   2  ║ Green           ║ Handsoap    ║                ║              ║
║     4118  ║   3  ║                 ║             ║                ║              ║
╚═══════════╩══════╩═════════════════╩═════════════╩════════════════╩══════════════╝

Intermediate demo where we again mark existing rows which must be updated:

WITH 
cte1 AS (
SELECT ItemsBefore.*, 
       CASE WHEN LAG(AllGifts) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NOT NULL AND AllGifts IS NULL THEN 1 ELSE 0 END update_AllGifts, 
       CASE WHEN LAG(HolidayGifts) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NOT NULL AND HolidayGifts IS NULL THEN 1 ELSE 0 END update_HolidayGifts,
       CASE WHEN LAG(ItemsBefore.PersonId) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NULL THEN 1 ELSE 0 END prev_not_exists
FROM ItemsBefore
JOIN updates ON ItemsBefore.PersonId = updates.PersonId
)
SELECT * FROM cte1;

╔═══════════╦══════╦═════════════════╦═════════════╦════════════════╦═══════════════╦══════════════════╦══════════════════════╦═════════════════╗
║ PersonId  ║ Pos  ║ FavoriteColors  ║  AllGifts   ║ BirthDayGifts  ║ HolidayGifts  ║ update_AllGifts  ║ update_HolidayGifts  ║ prev_not_exists ║
╠═══════════╬══════╬═════════════════╬═════════════╬════════════════╬═══════════════╬══════════════════╬══════════════════════╬═════════════════╣
║     1234  ║   1  ║ Blue            ║ Mug         ║ Mug            ║ Computer      ║               0  ║                   0  ║               1 ║
║     1234  ║   2  ║ Green           ║ Phone       ║ Candle         ║ Phone         ║               0  ║                   0  ║               0 ║
║     1234  ║   3  ║ Purple          ║ Candle      ║ Boardgame      ║               ║               0  ║                   1  ║               0 ║
║     1234  ║   4  ║ Yellow          ║ Computer    ║ Basketball     ║               ║               0  ║                   0  ║               0 ║
║     1234  ║   5  ║ Orange          ║ Boardgame   ║                ║               ║               0  ║                   0  ║               0 ║
║     1234  ║   6  ║ Red             ║ Basketball  ║                ║               ║               0  ║                   0  ║               0 ║
║     1234  ║   7  ║ Gray            ║             ║                ║               ║               1  ║                   0  ║               0 ║
║     1234  ║   8  ║ Brown           ║             ║                ║               ║               0  ║                   0  ║               0 ║
║     2222  ║   1  ║ Yellow          ║             ║                ║               ║               0  ║                   0  ║               1 ║
║     4118  ║   1  ║ Pink            ║ Clothes     ║ Handsoap       ║ Clothes       ║               0  ║                   0  ║               1 ║
║     4118  ║   2  ║ Green           ║ Handsoap    ║                ║               ║               0  ║                   1  ║               0 ║
║     4118  ║   3  ║                 ║             ║                ║               ║               1  ║                   0  ║               0 ║
╚═══════════╩══════╩═════════════════╩═════════════╩════════════════╩═══════════════╩══════════════════╩══════════════════════╩═════════════════╝

Using the previous query as CTE again, we update AllGifts values:


WITH 
cte1 AS (
SELECT ItemsBefore.*, 
       CASE WHEN LAG(AllGifts) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NOT NULL AND AllGifts IS NULL THEN 1 ELSE 0 END update_AllGifts, 
       CASE WHEN LAG(HolidayGifts) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NOT NULL AND HolidayGifts IS NULL THEN 1 ELSE 0 END update_HolidayGifts,
       CASE WHEN LAG(ItemsBefore.PersonId) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NULL THEN 1 ELSE 0 END prev_not_exists
FROM ItemsBefore
JOIN updates ON ItemsBefore.PersonId = updates.PersonId
)
UPDATE cte1
SET cte1.AllGifts = updates.gift
FROM updates
WHERE cte1.PersonId = updates.PersonId
  AND cte1.update_AllGifts + cte1.prev_not_exists = 1
  AND cte1.AllGifts IS NULL;

-- Using previous query as CTE, update HolidayGifts value
  
WITH 
cte1 AS (
SELECT ItemsBefore.*, 
       CASE WHEN LAG(AllGifts) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NOT NULL AND AllGifts IS NULL THEN 1 ELSE 0 END update_AllGifts, 
       CASE WHEN LAG(HolidayGifts) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NOT NULL AND HolidayGifts IS NULL THEN 1 ELSE 0 END update_HolidayGifts,
       CASE WHEN LAG(ItemsBefore.PersonId) OVER (PARTITION BY ItemsBefore.PersonId ORDER BY Pos) IS NULL THEN 1 ELSE 0 END prev_not_exists
FROM ItemsBefore
JOIN updates ON ItemsBefore.PersonId = updates.PersonId
)
UPDATE cte1
SET cte1.HolidayGifts = updates.gift
FROM updates
WHERE cte1.PersonId = updates.PersonId
  AND cte1.update_HolidayGifts + cte1.prev_not_exists = 1
  AND cte1.HolidayGifts IS NULL;
  
-- Final state, now the table looks like
  
SELECT *
FROM ItemsBefore
ORDER BY PersonId, Pos;

╔═══════════╦══════╦═════════════════╦═════════════╦════════════════╦══════════════╗
║ PersonId  ║ Pos  ║ FavoriteColors  ║  AllGifts   ║ BirthDayGifts  ║ HolidayGifts ║
╠═══════════╬══════╬═════════════════╬═════════════╬════════════════╬══════════════╣
║     1234  ║   1  ║ Blue            ║ Mug         ║ Mug            ║ Computer     ║
║     1234  ║   2  ║ Green           ║ Phone       ║ Candle         ║ Phone        ║
║     1234  ║   3  ║ Purple          ║ Candle      ║ Boardgame      ║ Book         ║
║     1234  ║   4  ║ Yellow          ║ Computer    ║ Basketball     ║              ║
║     1234  ║   5  ║ Orange          ║ Boardgame   ║                ║              ║
║     1234  ║   6  ║ Red             ║ Basketball  ║                ║              ║
║     1234  ║   7  ║ Gray            ║ Book        ║                ║              ║
║     1234  ║   8  ║ Brown           ║             ║                ║              ║
║     2222  ║   1  ║ Yellow          ║ Book        ║                ║ Book         ║
║     4000  ║   1  ║ Pink            ║ Clothes     ║ Handsoap       ║              ║
║     4118  ║   1  ║ Pink            ║ Clothes     ║ Handsoap       ║ Clothes      ║
║     4118  ║   2  ║ Green           ║ Handsoap    ║                ║ Book         ║
║     4118  ║   3  ║                 ║ Book        ║                ║              ║
╚═══════════╩══════╩═════════════════╩═════════════╩════════════════╩══════════════╝

I apologize for the delay. Thank you so much for this answer! I was able to analyze it a bit. I'm not sure this solution continues to work with an "updates" table that has multiple gifts per person. For example, 1234 gets a "Book" and 1234 gets a "TV". I was using one gift for simplicity but I should've stated that a person can have 1 or many gifts in the table. Again, thank you. — Mike S, Commented Nov 28, 2020 at 16:34

Stack Exchange Network

What would be the best way to conditionally insert or update a table that has this unique design?

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
t-sql
query
or ask your own question.

Hot Network Questions

What would be the best way to conditionally insert or update a table that has this unique design?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
t-sql
query
or ask your own question.