Update with Self Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update with Self Join

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-11 : 09:54:28

Steve writes "Here's the setup:

I'm creating a Sales data mart. I have a Sales table with sales data per time period. For ease of use, I'd like to store the prior period's sales data in each current period's row because many reports compare this period versus prior period. So, let's say I have Customer, Product, Store, and TIME_PERIOD foreign keys in the Sales table. Each Sales measure column is suffixed with _CP for current period and _PP for prior period. At the end of each period, i.e., end of week, I get a flat file feed containing the current period information.

Here's the problem:

I'm attempting to write an Update statement doing a self join to no avail. I'd like to issue a statement that updates the current week's prior period information from the prior period rows.

I'd rather not use last month's flat file, because we run a series of data cleaning, aggregation, etc. routines post load.

Here's the statement I'm attempting to get to work:

update
SALES
set
DOLLARS_PP = pp.DOLLARS,
ITEMS_PP = pp.ITEMS
from
SALES cp, SALES pp
where
cp.TIME_ID = @currPeriod
and pp.TIME_ID= @prevPeriod
and cp.CUSTOMER_ID = pp.CUSTOMER_ID
and cp.PRODUCT_ID = pp.PRODUCT_ID
and cp.STORE_ID = pp.STORE

The statement passes the syntax checker, but fails at run-time with an ambiguous table error. I've tried several alternative statements.

Suggestions? Thanks, I really appreciate it!"

mtomeo
Starting Member

30 Posts

Posted - 2003-06-11 : 11:08:23

I think you need to use your aliases in your SET Statement:

 
update 
SALES 
set 
cp.DOLLARS_PP = pp.DOLLARS, 
cp.ITEMS_PP = pp.ITEMS 
from 
SALES cp, SALES pp 
where 
cp.TIME_ID = @currPeriod 
and pp.TIME_ID= @prevPeriod 
and cp.CUSTOMER_ID = pp.CUSTOMER_ID 
and cp.PRODUCT_ID = pp.PRODUCT_ID 
and cp.STORE_ID = pp.STORE

dsdeming

479 Posts

Posted - 2003-06-11 : 13:15:45

I don't think you can alias the table being updated:

create table #a( a int, b int )

update #a
set a1.a = a2.b
from #a a1, #a a2
where a1.a = a2.a

-- Server: Msg 1032, Level 15, State 1, Line 4
-- Cannot use the column prefix 'a1'. This must match the
-- object in the UPDATE clause '#a'.

update #a
set #a.a = a2.b
from #a, #a a2
where #a.a = a2.a

-- The command(s) completed successfully.

Dennis

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-11 : 13:27:48

actually all you need to do is provide the alias for which table you want to update, since both the tables have the same name and you have aliased both the tables.

update
cp
set
cp.DOLLARS_PP = pp.DOLLARS,
cp.ITEMS_PP = pp.ITEMS
from
SALES cp, SALES pp
where
cp.TIME_ID = @currPeriod
and pp.TIME_ID= @prevPeriod
and cp.CUSTOMER_ID = pp.CUSTOMER_ID
and cp.PRODUCT_ID = pp.PRODUCT_ID
and cp.STORE_ID = pp.STORE

Owais

slevander
Starting Member

1 Post

Posted - 2003-06-14 : 10:26:33

Hi - Steve here - thanks for the responses. Actually before seeing the last alias message ("Update CP"), I was able to solve it by creating a table variable (more efficient than a temp table), inserting the prior period's values and keys into it, then updating the Sales table from the table variable. I would have liked to acheived it in one statement, as I think not using a table variable would be more efficient.

I'll have to try the "update cp" technique when I get back to work on Monday!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources