CODEWITHSUNDEEP
sql-serversql-updateself-join
J.S.Orris
J.S.Orris

Reputation: 4821

UPDATING via SELF JOIN with duplicate ID's

The following is an example schema:

CREATE TABLE #temp1(
id varchar(10),
a int,
b int, 
c int)


INSERT INTO #temp1 (id, a, b, c)
VALUES ('one',1, 0, 0)

INSERT INTO #temp1 (id, a, b, c)
VALUES ('one',0, 1, 0)

INSERT INTO #temp1 (id, a, b, c)
VALUES ('two',0, 1, 0)

INSERT INTO #temp1 (id, a, b, c)
VALUES ('two',0, 0, 1)

INSERT INTO #temp1 (id, a, b, c)
VALUES ('three',0, 0, 1)

INSERT INTO #temp1 (id, a, b, c)
VALUES ('three',1, 0, 0)

Which provides the following SELECT results:

id   |a|b|c|
-----------
one  |1|0|0|
------------
one  |0|1|0|
------------
two  |0|1|0|
------------
two  |0|0|1|
------------
three|0|0|1|
------------
three|1|0|0|

I want to UPDATE via a SELF JOIN so that I can make duplicate rows per ID as follows:

    id   |a|b|c|
    -----------
    one  |1|1|0|
    ------------
    one  |1|1|0|
    ------------
    two  |0|1|1|
    ------------
    two  |0|1|1|
    ------------
    three|1|0|1|
    ------------
    three|1|0|1|

I am attempting to accomplish creating the duplicate results from a SELF JOIN UPDATE as follows; this does not produce the results I want:

UPDATE o 
SET 
o.a = t.a,
o.b = t.b, 
o.c = t.c
FROM #temp1 o
INNER JOIN #temp1 t
ON o.id = t.id
WHERE (o.a = 0 AND t.a = 1)
OR (o.b = 0 AND t.b = 1)
OR (o.c = 0 AND t.c = 1)

Am I taking the wrong approach in getting my desired results? Is this something that a merge can do faster?

Upvotes: 1

Views: 36

Answers (1)

A_Sk
A_Sk

Reputation: 4630

Try:

Max(column_Name) With OVER Clause

You'll get the desired output.

select 
 Id,
 Max(a) over(partition by Id),
 Max(b) over(partition by Id),
 Max(c) over(partition by Id)
from #temp1

Upvotes: 1

Related Questions