update from - 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

SQL Server Development (2000)

update from

Author

Topic

jraha
Starting Member

16 Posts

Posted - 2002-04-29 : 13:46:26

create table #temp
(ClientNum int, Firmname varchar(100), VendorNum int, Status int, CurrencyFormat int)

alter table #temp Add [04/01/2001] real default 0

create table #holder
(ClientNum int, VendorNum int, FirmName varchar(100), Ondate datetime , Status int, CurrencyFormat int, BIAmount real, ClientAmount real)

Update #temp set [04/01/2001] = isnull(ClientAmount,0)
from #temp T
join (select VendorNum, Status, CurrencyFormat, Ondate,
sum(clientAmount) as ClientAmount from #holder
Group by VendorNum, status, currencyFormat, Ondate
) as H
on H.VendorNum = T.VendorNum and
H.Status = T.Status and
H.CurrencyFormat = T.CurrencyFormat
where Ondate = '04/01/2001'

/*
I get an error on the update statement saying the following:
The column prefix 'H' does not match with a table name or aliasname used in the query.

However, if I replace the 'update ... from' with a 'select * from' it recognizes the derived table and will execute the select statement with no trouble.
*/

select *
from #temp T
join (select VendorNum, Status, CurrencyFormat, Ondate,
sum(clientAmount) as ClientAmount from #holder
Group by VendorNum, status, currencyFormat, Ondate
) as H
on H.VendorNum = T.VendorNum and
H.Status = T.Status and
H.CurrencyFormat = T.CurrencyFormat
where Ondate = '04/01/2001'

Any clues?
thanks,
Joel

dsdeming

479 Posts

Posted - 2002-04-29 : 14:19:23

What version of SQL are you using? I pasted your code into a Query Analyzer window in SQL 2K and it ran without errors.

jraha
Starting Member

16 Posts

Posted - 2002-04-29 : 14:32:52

I'm on sql 7.0

izaltsman
A custom title

1139 Posts

Posted - 2002-04-29 : 15:07:15

Since you aliased your table in the FROM clause, use that alias (instead of the full table name) after the UPDATE keyword:

Update T
set [04/01/2001] = isnull(ClientAmount,0)
from #temp T
join (select VendorNum, Status, CurrencyFormat, Ondate,
sum(clientAmount) as ClientAmount from #holder
Group by VendorNum, status, currencyFormat, Ondate
) as H
on H.VendorNum = T.VendorNum and
H.Status = T.Status and
H.CurrencyFormat = T.CurrencyFormat
where Ondate = '04/01/2001'

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

Edited by - izaltsman on 04/29/2002 15:09:01

jraha
Starting Member

16 Posts

Posted - 2002-04-29 : 15:46:29

Yup, that fixed it... I don't quite see why it wouldn't work before, but I guess I'll have to be more strict with my aliasing.

Thanks so much,
Joel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources