T-SQL: Can I update a table using rows from another table?

Question

I have a table that looks like this

[UniqueID]-[1]-[2]-[3]-[etc... to 250
------------------------------
00000A    | 0 | 0 | 1 |
00000B    | 0 | 1 | 0 |
00000C    | 0 | 1 | 1 |

I pivoted that table from another table with two columns, the unique ID and the number (1, 2, 3, etc.)

I have another table which has two columns, a number and an elimination. For example, if the number is 2 and the elimination is 3, I would take the unique ID 00000C, go to column 3, and change the 1 to a 0.

In the past, I have manually written:

UPDATE [TABLE] SET [3] = 0 WHERE [2] = 1

I need to do this about 150 times, so it would be much more concise to write a query to read from the second table to modify the first. Additionally, when I have to make changes, I will only need to modify the table instead of making changes to the query itself.

I know I can probably do this by pivoting the second table and using Dynamic SQL, and I will do that if I have to, but I'm wondering if any of you have some other ideas to solve this problem.

Basically, what I am looking to do is this:

UPDATE [TABLE] SET [(SELECT elim FROM ElimTbl)] = 0 
WHERE [(SELECT num FROM ElimTbl)] = 1

I know that's invalid, but I'm hoping someone has a better idea.

Thank you for your time!

Answer 1

Target table is not normalized because [1], [2], ..., [150] are nothing more than a repeating group of columns (1, 2). This means that target table breaks first normal form. This problem generates another problem: UPDATE statement should include the same expression with small modifications (a [sub]query that finds elimination) 150th times.

Instead, I would use a normalized target table and when is needed, data from target table can be easily pivoted using PIVOT operator:

/*
[UniqueID]-[1]-[2]-[3]-etc... 150
------------------------------
00000A    | 0 | 0 | 1 |
00000B    | 0 | 1 | 0 |
00000C    | 0 | 1 | 1 |
*/

DECLARE @Target TABLE (
    UniqueID    VARCHAR(6) NOT NULL,
    Num         INT NOT NULL,   
        PRIMARY KEY (UniqueID, Num),
    Value       BIT NOT NULL
);
INSERT  @Target 
VALUES  
('00000A', 3, 1),
('00000B', 2, 1),
('00000C', 2, 1), ('00000C', 3, 1);

DECLARE @Source TABLE (
    UniqueID    VARCHAR(6) NOT NULL,
        PRIMARY KEY (UniqueID),
    Num         INT NOT NULL
);
INSERT  @Source 
VALUES  
('00000B', 3), 
('00000C', 2);

SELECT * FROM @Target
SELECT * FROM @Source

-- Intermediate query

SELECT  s.*, x.*
FROM    @Source s
OUTER APPLY (
    SELECT  TOP(1) *
    FROM    @Target t
    WHERE   t.Num = s.Num
    AND     t.Value = 1
    AND     t.UniqueID >= s.UniqueID
    ORDER BY t.UniqueID 
) x
/*
Results
UniqueID Num UniqueID Num Value
-------- --- -------- --- -----
00000B   3   00000C   3   1
00000C   2   00000C   2   1
*/

-- Final query

UPDATE  t           --| or DELETE t 
SET     Value = 0   --| 
FROM    @Target AS t
WHERE   EXISTS (
    SELECT  *
    FROM    @Source s
    CROSS APPLY (
        SELECT  TOP(1) *
        FROM    @Target t
        WHERE   t.Num = s.Num
        AND     t.Value = 1
        AND     t.UniqueID >= s.UniqueID
        ORDER BY t.UniqueID 
    ) x
    WHERE x.UniqueID = t.UniqueID
)

SELECT * FROM @Target
/*
Results:
UniqueID Num         Value
-------- ----------- -----
00000A   3           1
00000B   2           1
00000C   2           0
00000C   3           0
*/

-- Pivot

;WITH CteSource 
AS 
(SELECT UniqueID, Num, CONVERT(TINYINT, Value) AS ValueAsInt FROM @Target)
SELECT  pvt.*
FROM    CteSource s
PIVOT( MAX(s.ValueAsInt) FOR s.Num IN ([1], [2], [3], /*...*/ [150]) ) pvt
/*
UniqueID 1    2    3    150
-------- ---- ---- ---- ----
00000A   NULL NULL 1    NULL --> NULLs can be replaced with 0 with ISNULL / COALESCE
00000B   NULL 1    NULL NULL
00000C   NULL 0    0    NULL
*/

Answer 2

Update t1
Set t1.value = t2.value
FROM
t1
INNER JOIN t2 ON t1.KEY = t2.KEY

Answer 3

After taking some time, I found an answer that works even better and is simpler.

My original table looks like this:

[Table A]
ID | Num
--------
A  | 3
B  | 2
C  | 2
C  | 3

My other table is:

Num | Eliminate
---------------
2   | 3

Basically that means that if any given ID is assigned both the number 2 and 3, 3 should be eliminated while leaving 2. If I join these tables, I get this:

ID | Num | Eliminate
--------------------
A  | 3   | NULL
B  | 2   | 3
C  | 2   | 3
C  | 3   | NULL

I can then produce a table with ID and Eliminate (without nulls):

[Table B]
ID | Eliminate
---------------
B  | 3
C  | 3

Finally, I can produce an output using the following:

SELECT [ID], [Num] FROM [Table A]
EXCEPT
SELECT [ID], [Eliminate] FROM [Table B]

This produces the output:

ID | Num
--------
A  | 3
B  | 2
C  | 2

Which is what I'm looking for, now I can pivot it if I would like or perform any other operations. This solution was much simpler than I thought, but I just didn't see it until a colleague suggested it... Hopefully this can help someone else stuck in the same situation!