Help with an UPDATE - 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)

Help with an UPDATE

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-11-13 : 08:24:04

Good morning

I was hoping someone could lend some assistance concerning this problem. I am trying to update a column in a table. However when I attempt to run it, I get the following error message:

"Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.TransNet Corporation_Test$Sales Invoice Header' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.TransNet Corporation_Test$Sales Invoice Header' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.TransNet Corporation_Test$Sales Invoice Header' does not match with a table name or alias name used in the query."

Here is my transact:

update dbo.[TransNet Corporation_Test$Sales Commission]
set [Cust PO No] = dbo.[TransNet Corporation_Test$Sales Invoice Header].[Customer PO_ No_]
where (dbo.[TransNet Corporation_Test$Sales Invoice Header].[No_] = [Inv No])
AND(dbo.[TransNet Corporation_Test$Sales Invoice Header].[Posting Date] = [Posting Date])

Thanks
Pharoah35

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-13 : 08:30:48

[code]update t1
set [Cust PO No] = t2.[Customer PO_ No_]
From dbo.[TransNet Corporation_Test$Sales Commission] t1
join dbo.[TransNet Corporation_Test$Sales Invoice Header] t2
on t2.[No_] = t1.[Inv No]
AND t2.[Posting Date] = t1.[Posting Date][/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 08:33:59

Try

update T
set [Cust PO No] = S.[Customer PO_ No_]
from dbo.[TransNet Corporation_Test$Sales Commission] T
inner join dbo.[TransNet Corporation_Test$Sales Invoice Header] S
on S.[No_] = [Inv No])
AND S.[Posting Date] = [Posting Date]

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 08:34:30

Madhivanan

Failing to plan is Planning to fail

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-11-13 : 08:42:38

Hi harsh_athalye

I just wanted to thank you so much for your assistance. This worked wonderfully

Thanks again
Pharoah35

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources