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.
Option |IDNumber |Birthdate
1 |123 |19900101
1 |234 |19800101
3 |123 |
3 |234 |
DECLARE @temp TABLE (
INSERT INTO @temp (idnumber)
OPTION = 1
AND IDNumber IN (SELECT IDNumber FROM Member WHERE OPTION = 3)
SET BirthDate = m2.BirthDate
FROM Member m2
INNER JOIN @temp m ON m.idnumber = m2.IDNumber
OPTION = 3
update member set BirthDate = (select BirthDate from Member where
Option=1 and IDNumber in(select IDNumber from Member
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
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