sql server update from select

Question

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.

Are you sure otherTable.key_field is valid? We can't say without seeing the *some complex query here*. — Fosco, Jun 06 '11 at 19:13
@Fosco The select query has all the referenced fields and runs fine by itself. — , Jun 06 '11 at 19:15
@TheOtherGuy your posted query also has a period at the end.... is that there in what you're running?... You have to give us the real query or we cannot see what's wrong. The concept/syntax is correct, but you're missing something and need more eyes on it. — Fosco, Jun 06 '11 at 19:18
Take a careful look at all of your references to `otherTable` in the query? Are they all spelled correctly? — Joe Stefanelli, Jun 06 '11 at 19:23
@Fosco I have included the actual query I have. @Joe I think I have every thing spelled correctly. — , Jun 06 '11 at 19:31
@TheOtherGuy so the error for the real query is that the column prefix 'summary' does not match? — Fosco, Jun 06 '11 at 19:32

score 1 · Accepted Answer · answered Jun 06 '11 at 19:36

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>.

actually they are not 0. I was just trying something silly but I forgot to revert back. I updated the query immediatly. would those be `summer.SumSLR` instead of 0, in that case? — , Jun 06 '11 at 19:38

score 0 · Answer 2 · answered Jun 06 '11 at 19:24

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.

score 0 · Answer 3 · answered Jun 06 '11 at 19:44

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

Hey thanks but I think I was just mis-using alias. See @Fosco's answer that fixed the issue for me. Appreciate the effort though. — , Jun 06 '11 at 20:08

sql server update from select

3 Answers3

Linked