从选择更新sql server [英] 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:
列前缀 'otherTable' 确实与表名或别名不匹配查询中使用的名称.
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.
(我正在使用 *blush* sql server 2000.)
(I'm using *blush* sql server 2000.)
这是实际查询
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
而实际的错误信息是
服务器:消息 107,级别 16,状态 2,第 1 行列前缀 'summary'与表名不匹配或查询中使用的别名.
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.
推荐答案
我向您提交了这个更改后的查询:
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
此处明显不同:我不会在复杂查询的内部和外部重复使用别名 pr
.我按照我喜欢的方式对连接重新排序(以前先引用表),并在 2 个地方明确标记了 pr_pk.我还更改了更新语法以使用 update
.
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>
.
这篇关于从选择更新sql server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!