Nicholas Aysen Nicholas Aysen - 16 days ago 6
SQL Question

How to update a SQL-Server 2008 table with records from the same table with different ID values

I have a Member table with IDNumber, Birthdate, Option and ID columns. The ID is a guid and is the PK.

Now, some Members are on Option 1 and have all the columns filled with data (IDNumber, Birthdate).
Some Members are on other Options.
In this example, there are members on Option 3, who have the same IDNumbers as the Members on Option 1 (they are in fact the same Members, with multiple options).
However, the Birthdate column for these members is empty.
I am trying to update my table to match the Birthdate values from Members on Option 1 to reflect on Option 3.

In essence,

Option |IDNumber |Birthdate
-----------------------------------
1 |123 |19900101
1 |234 |19800101
3 |123 |
3 |234 |


How would I go about updating to reflect the missing birthdates for Option 3?
I have attempted so far:

DECLARE @temp TABLE (
--birthdate int,
idnumber VARCHAR(13)
)

INSERT INTO @temp (idnumber)
SELECT IDNumber
FROM Member
WHERE
OPTION = 1
AND IDNumber IN (SELECT IDNumber FROM Member WHERE OPTION = 3)

UPDATE Member
SET BirthDate = m2.BirthDate
FROM Member m2
INNER JOIN @temp m ON m.idnumber = m2.IDNumber
WHERE
OPTION = 3


Which says it affected the records, but not the members under Option 3.

I also tried to do:

update member set BirthDate = (select BirthDate from Member where
Option=1 and IDNumber in(select IDNumber from Member
where Option=3))
where Option=3


Which returns and error of


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


Please could you assist in pointing out where I have gone wrong?

Answer
    UPDATE member SET Birthdate = A.Birthdate
    FROM 
    (
        SELECT tOption ,IDNumber ,Birthdate
        FROM member 
        WHERE Birthdate > 0
    ) A WHERE A.IDNumber = member.IDNumber AND member.Birthdate = 0
Comments