Need help with update SQL with subquery
-
Hi all, I'm trying to write an update statement with a subquery and can't quite get it right. Hoping you can help. Overall goal of the stored procedure I'm writing is to merge data and make sure I have no duplicates and that the responses associated with a potential duplicate are re-associated with the original record instead of the duplicate. So...... Basic structure of tables is this: Rosters table - contains all of the rosters in the database RosterEntries table - contains a single record for each person in a roster. FK here is back to the rosters table. ParticipantResponses - contains a record for each response a person gave. FK here is back to the RosterEntries table. I am merging the rosters. So when I'm done, there will be one roster where there were two. During the merge, I need to make sure that no entries in the RosterEntries table is duplicated and also make sure that the responses that are tied to a duplicate record are reassociated with the original, not the duplicate. I have an @Existing table variable. This is a subset of the RosterEntries table and it contains only those records that are associated with the original roster. I have the @Dupes table variable. This is a subset of RosterEntries that contains only those records from the roster to be merged (the one that will be going away) which are duplicates of those already in the RosterEntries table. So, let's say James Kirk is in the RosterEntries table twice, once associated with RosterID1 and once associated with RosterID2. RosterID2 is going away. So James Kirk's first record would be in my @Existing table variable. James Kirk's 2nd record will be in my @Dupes table and this is the one I want to get rid of. But before I do that, I have to associate his responses with that first record (the RosterID1 one) so I don't have orphaned responses. Basically, I'm trying to replace the FK in ParticipantResponses so that it points to Kirk's first record (RosterID1 record) not the 2nd one (RosterID2 record) The SQL statement I've been working on is this:
update participantresponses pr, @dupes d
set pr.rostEntID =
(
select e.recid
from @Existing e, @dupes d2
where
e.refid = d2.refid
and e.palias = d2.palias
and e.palias2 = d2.palias2
and e.pweight = d2.pweight
and e.pHandicap = d2.phandicap
and e.loginID = d2.loginid
and e.password = d2.password
)
where pr.rostEntID = d.recID(I know... there's a lot of where clause activity in the subquery, but
-
Hi all, I'm trying to write an update statement with a subquery and can't quite get it right. Hoping you can help. Overall goal of the stored procedure I'm writing is to merge data and make sure I have no duplicates and that the responses associated with a potential duplicate are re-associated with the original record instead of the duplicate. So...... Basic structure of tables is this: Rosters table - contains all of the rosters in the database RosterEntries table - contains a single record for each person in a roster. FK here is back to the rosters table. ParticipantResponses - contains a record for each response a person gave. FK here is back to the RosterEntries table. I am merging the rosters. So when I'm done, there will be one roster where there were two. During the merge, I need to make sure that no entries in the RosterEntries table is duplicated and also make sure that the responses that are tied to a duplicate record are reassociated with the original, not the duplicate. I have an @Existing table variable. This is a subset of the RosterEntries table and it contains only those records that are associated with the original roster. I have the @Dupes table variable. This is a subset of RosterEntries that contains only those records from the roster to be merged (the one that will be going away) which are duplicates of those already in the RosterEntries table. So, let's say James Kirk is in the RosterEntries table twice, once associated with RosterID1 and once associated with RosterID2. RosterID2 is going away. So James Kirk's first record would be in my @Existing table variable. James Kirk's 2nd record will be in my @Dupes table and this is the one I want to get rid of. But before I do that, I have to associate his responses with that first record (the RosterID1 one) so I don't have orphaned responses. Basically, I'm trying to replace the FK in ParticipantResponses so that it points to Kirk's first record (RosterID1 record) not the 2nd one (RosterID2 record) The SQL statement I've been working on is this:
update participantresponses pr, @dupes d
set pr.rostEntID =
(
select e.recid
from @Existing e, @dupes d2
where
e.refid = d2.refid
and e.palias = d2.palias
and e.palias2 = d2.palias2
and e.pweight = d2.pweight
and e.pHandicap = d2.phandicap
and e.loginID = d2.loginid
and e.password = d2.password
)
where pr.rostEntID = d.recID(I know... there's a lot of where clause activity in the subquery, but
If you are using SQL Server then try
update pr
set rostEntID =
(
select TOP 1 e.recid
from @Existing e, @dupes d2
where
e.refid = d2.refid
and e.palias = d2.palias
and e.palias2 = d2.palias2
and e.pweight = d2.pweight
and e.pHandicap = d2.phandicap
and e.loginID = d2.loginid
and e.password = d2.password
)
FROM participantresponses pr
INNER JOIN @dupes d
ON pr.rostEntID = d.recIDNotice the TOP 1 was added to prevent multiples.
-
If you are using SQL Server then try
update pr
set rostEntID =
(
select TOP 1 e.recid
from @Existing e, @dupes d2
where
e.refid = d2.refid
and e.palias = d2.palias
and e.palias2 = d2.palias2
and e.pweight = d2.pweight
and e.pHandicap = d2.phandicap
and e.loginID = d2.loginid
and e.password = d2.password
)
FROM participantresponses pr
INNER JOIN @dupes d
ON pr.rostEntID = d.recIDNotice the TOP 1 was added to prevent multiples.
That did it! Thanks a million!!!
Denise Duggan