What's wrong with this code - 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)

What's wrong with this code

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-09 : 17:16:03

Hi,
I have written a stored procedure. This one is giving error while trying to
compile.
CODE:
create proc sp_update_dbo_PWO_Status_6
as
create table tempCompany(
Company varchar (50),
prefix varchar (4),
number varchar (4),
revision varchar(2),
pricerevision varchar(2)
)

insert into tempCompany(
company,
prefix,
number,
revision,
pricerevision
)
(select dbo_Contacts.Company,prefix, number, revision, pricerevision
FROM dbo_PWOContacts INNER JOIN dbo_Contacts ON
(dbo_PWOContacts.ContactNumber = dbo_Contacts.ContactNumber)
AND (dbo_PWOContacts.CCCCode = dbo_Contacts.CCCCode))

select * from tempCompany

update dbo_PWO_Status
set dbo_PWO_Status.companyname
= tempCompany.company
where
(dbo_PWO_Status.prefix = tempCompany.prefix) AND
(dbo_PWO_Status.number = tempCompany.number) AND
(dbo_PWO_Status.revision = tempCompany.revision) AND
(dbo_PWO_Status.pricerevision = tempCompany.pricerevision)
--drop table tempCompany

Once I figure out that this is working I will convert the tempCompany to
#tmp table. However I am getting the following result:

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

Any help is appreciated. Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-09 : 18:53:49

Try

update P
set P.companyname
= T.company
from dbo_PWO_Status P inner join tempCompany T
on
(P.prefix = T.prefix) AND
(P.number = T.number) AND
(P.revision = T.revision) AND
(P.pricerevision = T.pricerevision)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources