SQL UPDATE using two joined subqueries

Question

I wrote a query that does an inner join of two subqueries. The first subquery has an alias of "SRC" and the other has an alias of "DEST". What I want to do is update some fields in the table NomineeActionLegislatorVoteDetail (part of DEST subquery) with values from the table Nominee_Committee_Vote (part of SRC subquery). It souds easy but I just cannot figure out how to do it. Does anyone have any suggestions? Any help would be appreciated.

Here is the query I wrote:

select * 
from (
    select  ncv.*,
        na.NomineeActionId,
        l.LegislatorId
    from ongoing..Nominee_Committee_Vote ncv
    inner join azleg..NomineeAction na on
        ncv.session_id    = na.x_session_id   and
        ncv.committee_id  = na.x_committee_id and
        ncv.agency_id     = na.x_agency_id    and
        ncv.position_id   = na.x_position_id  and
        ncv.nominee_id    = na.x_nominee_id   and
        ncv.received_date = na.x_received_date
    inner join status..session s on
        ncv.session_id    = s.session_id
    inner join azleg..Legislator l on
        ncv.member_id     = l.x_member_id     and
        s.legislature     = l.LegislatureId
) SRC
inner join (
    select votedetail.*
    from azleg..NomineeActionLegislatorVoteDetail votedetail
    inner join azleg..NomineeAction nom_action on
        votedetail.NomineeActionId = nom_action.NomineeActionId
) DEST on
    SRC.agency_id     = DEST.x_agency_id     and
    SRC.position_id   = DEST.x_position_id   and
    SRC.nominee_id    = DEST.x_nominee_id    and
    SRC.received_date = DEST.x_received_date and
    SRC.session_id    = DEST.x_session_id    and
    SRC.committee_id  = DEST.x_committee_id  and
    SRC.member_id     = DEST.x_member_id
where   SRC.NomineeActionId <> DEST.NomineeActionId
   OR   SRC.LegislatorId <> DEST.LegislatorId
   OR   SRC.Vote <> DEST.Vote

The question is unclear to me. What table do you need to update? — AXMIM, Mar 31 '15 at 19:58
UPDATE TABLENAME SET Column = Sub.Field FROM TABLENAME JOIN (SUBQUERY) AS Sub ON TABLENAME.ID = Sub.ID — AXMIM, Mar 31 '15 at 20:00
You need to include which of the two tables you want to update. You should also indicate which fields you want to update and with which values. Also Inequality as a joining condition will likely cause problems with an update if more than one value in source satisfies the join condition. How will you pick the correct row? — Conrad Frix, Mar 31 '15 at 20:01
possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — Tab Alleman, Mar 31 '15 at 20:05
I have revised the question. It is different from other questions because because I am dealing with two subqueries (SRC and DEST) — Travis Lovellette, Mar 31 '15 at 21:13
No, it's really still the same as the link @TabAlleman posted. Two derived tables (sub-queries) is really no different than two actual tables. — Andrew, Mar 31 '15 at 22:14

score 0 · Answer 1 · answered Mar 31 '15 at 22:08

Can you insert the update in front of the sub queries

UPDATE NomineeActionLegislatorVoteDetail 
SET NomineeActionLegislatorVoteDetail.COLUMNNAME = src.VALUE
--SubQueriesBelow
from
(select ncv.*, na.NomineeActionId, l.LegislatorId from ongoing..Nominee_Committee_Vote ncv
inner join azleg..NomineeAction na on
ncv.session_id = na.x_session_id and
ncv.committee_id = na.x_committee_id and
ncv.agency_id = na.x_agency_id and
ncv.position_id = na.x_position_id and
ncv.nominee_id = na.x_nominee_id and
ncv.received_date = na.x_received_date
inner join status..session s on
ncv.session_id = s.session_id
inner join azleg..Legislator l on
ncv.member_id = l.x_member_id and
s.legislature = l.LegislatureId) SRC
inner join
(select votedetail.* from azleg..NomineeActionLegislatorVoteDetail votedetail
inner join azleg..NomineeAction nom_action on
votedetail.NomineeActionId = nom_action.NomineeActionId) DEST on
SRC.agency_id = DEST.x_agency_id and
SRC.position_id = DEST.x_position_id and
SRC.nominee_id = DEST.x_nominee_id and
SRC.received_date = DEST.x_received_date and
SRC.session_id = DEST.x_session_id and
SRC.committee_id = DEST.x_committee_id and
SRC.member_id = DEST.x_member_id
where SRC.NomineeActionId <> DEST.NomineeActionId
OR SRC.LegislatorId <> DEST.LegislatorId
OR SRC.Vote <> DEST.Vote

It looks like the update is on a table in the sub query so it could be re factored to have one subquery

score 0 · Answer 2 · answered Mar 31 '15 at 22:15

I've adjusted the lay-out of your query a bit to disclose better where subqueries start and end. In your DEST subquery, you join NomineeAction but you don't select any columns from it, so it only filters rows from NomineeActionLegislatorVoteDetail for which no NomineeAction exists. So really you wanna be updating and joining on columns from NomineeActionLegislatorVoteDetail exclusively. Based on that, I would write the update query like so:

update votedetail set
    votedetail.SomeColumn = SomeValue,
    votedetail.SomeOtherColumn = SomeOtherValue
from azleg..NomineeActionLegislatorVoteDetail votedetail
join azleg..NomineeAction nom_action on
        votedetail.NomineeActionId = nom_action.NomineeActionId
join (
    select  ncv.*,
        na.NomineeActionId,
        l.LegislatorId
    from ongoing..Nominee_Committee_Vote ncv
    inner join azleg..NomineeAction na on
        ncv.session_id    = na.x_session_id   and
        ncv.committee_id  = na.x_committee_id and
        ncv.agency_id     = na.x_agency_id    and
        ncv.position_id   = na.x_position_id  and
        ncv.nominee_id    = na.x_nominee_id   and
        ncv.received_date = na.x_received_date
    inner join status..session s on
        ncv.session_id    = s.session_id
    inner join azleg..Legislator l on
        ncv.member_id     = l.x_member_id     and
        s.legislature     = l.LegislatureId
) SRC on
    SRC.agency_id     = votedetail.x_agency_id     and
    SRC.position_id   = votedetail.x_position_id   and
    SRC.nominee_id    = votedetail.x_nominee_id    and
    SRC.received_date = votedetail.x_received_date and
    SRC.session_id    = votedetail.x_session_id    and
    SRC.committee_id  = votedetail.x_committee_id  and
    SRC.member_id     = votedetail.x_member_id
where   SRC.NomineeActionId <> votedetail.NomineeActionId
   OR   SRC.LegislatorId <> votedetail.LegislatorId
   OR   SRC.Vote <> votedetail.Vote

SQL UPDATE using two joined subqueries

2 Answers2