sql server update from select

问题:

Following the answer from this post, I have something like this:

update MyTable
set column1 = otherTable.SomeColumn,
    column2 = otherTable.SomeOtherColumn
from MyTable
inner join
(select *some complex query here*) as otherTable
on MyTable.key_field = otherTable.key_field;

However, I keep getting this error:

The column prefix 'otherTable' does not match with a table name or alias name used in the query.

I'm not sure what's wrong. Can't I do such an update from a select query like this? Any help would be greatly appreciated.

(I'm using *blush* sql server 2000.)

EDIT:

here's the actual query

update pdx_projects set pr_rpc_slr_amount_year_to_date = summary.SumSLR, pr_rpc_hours_year_to_date = summary.SumHours
    from pdx_projects pr join (
select  pr.pr_pk pr_pk, sum(tc.stc_slr_amount)  SumSLR, sum(tc.stc_worked_hours)  SumHours from pdx_time_and_cost_from_rpc tc 
    join pdx_rpc_projects sp on tc.stc_rpc_project_id = sp.sol_rpc_number
    join pdx_rpc_links sl on sl.sol_fk = sp.sol_pk
    join pdx_projects pr on pr_pk = sl.pr_fk
    where tc.stc_time_card_year = year(getdate())
    group by pr_pk
) as summary

on pr.pr_pk = summary.pr_pk

and the actual error message is

Server: Msg 107, Level 16, State 2, Line 1 The column prefix 'summary' does not match with a table name or alias name used in the query.

回答1:

I submit to you this altered query:

update x
set x.pr_rpc_slr_amount_year_to_date = summary.sumSLR, 
    x.pr_rpc_hours_year_to_date = summary.sumHours
from pdx_projects x
join (
    select  pr.pr_pk as pr_pk, 
            sum(tc.stc_slr_amount) as SumSLR, 
            sum(tc.stc_worked_hours) as SumHours 
    from pdx_time_and_cost_from_rpc tc 
    join pdx_rpc_projects sp on tc.stc_rpc_project_id = sp.sol_rpc_number
    join pdx_rpc_links sl on sp.sol_pk = sl.sol_fk
    join pdx_projects pr on sl.pr_fk = pr.pr_pk
    where tc.stc_time_card_year = year(getdate())
    group by pr.pr_pk
) as summary
on x.pr_pk = summary.pr_pk

Notably different here: I don't re-use the alias pr inside and outside of the complex query. I re-ordered the joins the way I like them (previously referenced table first,) and explicitly notated pr_pk in 2 places. I also changed the update syntax to use update <alias>.

回答2:

Maybe not the answer you're looking for, but instead of generating hugely complex queries, I usually default to inserting the some complex query here into a table variable. Then you can do a simple update to MyTable with a join to the table variable. It may not be quite as efficient, but its much easier to maintain.

回答3:

I couldn't replicate your error using SQL 2008 in 80 compatibility level. While this option doesn't guarantee that I'll get the same results as you, nothing appears to be out of place.

create table pdx_projects
(
    pr_rpc_slr_amount_year_to_date varchar(max)
    , pr_rpc_hours_year_to_date varchar(max)
    , pr_pk varchar(max)

)

create table pdx_time_and_cost_from_rpc
(
    stc_slr_amount decimal
    , stc_worked_hours decimal
    , stc_rpc_project_id varchar(max)
    , stc_time_card_year varchar(max)
)

create table  pdx_rpc_projects
(
    sol_rpc_number varchar(max)
    , sol_pk varchar(max)
)

create table pdx_rpc_links
(
    sol_fk varchar(max)
    , pr_fk varchar(max)
)


update pdx_projects
set
    pr_rpc_slr_amount_year_to_date = summary.SumSLR
    , pr_rpc_hours_year_to_date = summary.SumHours
from
    pdx_projects pr
join (
    select  pr.pr_pk pr_pk
        , sum(tc.stc_slr_amount)  SumSLR
        , sum(tc.stc_worked_hours)  SumHours
    from pdx_time_and_cost_from_rpc tc 
    join pdx_rpc_projects sp on tc.stc_rpc_project_id = sp.sol_rpc_number
    join pdx_rpc_links sl on sl.sol_fk = sp.sol_pk
    join pdx_projects pr on pr_pk = sl.pr_fk
    where tc.stc_time_card_year = year(getdate())
    group by pr_pk
    ) as summary
    on pr.pr_pk = summary.pr_pk