Solved

T-SQL UPDATE error?

Posted on 2004-10-07
7
748 Views
Last Modified: 2012-08-14
My code:

UPDATE [ACCTG].[dbo].[Co$AgileItem]
SET [Mfg Name]=[ACCTG].[dbo].[Co$AgileOra].[Mfg Name]

and I get the following error?

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'ACCTG.dbo.Co$AgileOra' does not match with a table name or alias name used in the query.

What gives????
0
Comment
Question by:mtnbuzz
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 21

Expert Comment

by:mastoo
Comment Utility
UPDATE [ACCTG].[dbo].[Co$AgileItem]
SET [Mfg Name]=[ACCTG].[dbo].[Co$AgileOra].[Mfg Name]
FROM [ACCTG].[dbo].[Co$AgileItem], [ACCTG].[dbo].[Co$AgileOra]
WHERE PutSomeKindOfJoinClauseHere
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
UPDATE [ACCTG].[dbo].[Co$AgileItem]   <--- this is the table to be updated
SET [Mfg Name]=[ACCTG].[dbo].[Co$AgileOra].[Mfg Name]  <--- doesn't specify the same table name

and you haven't provided a From clause to specify the relationship
between the rows in [Co$AgileItem]
and [Co$AgileOra]

what are you actually trying to do?
0
 

Author Comment

by:mtnbuzz
Comment Utility
Create on UPDATE stmt to update 2 columns [Mfg] and [Mfg Part Number] from table [ACCTG].[dbo].[Co$AgileOra].[Mfg Name] to [ACCTG].[dbo].[Co$AgileItem] with the exact same data just missing the Mfg info.


[ACCTG].[dbo].[Co$AgileOra]                   [ACCTG].[dbo].[Co$AgileItem]
[No.]   09-0200-001                                 [No.] 09-0200-001
[Descrip] Test                                         [Descrip] Test
[Mfg] Jones                                             [Mfg] NULL
[Mfg Part Number] 123                             [Mfg Part Number] NULL

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
update [Acctg].[dbo].[Co$AgileItem]
  set A.[Mfg] = B.[Mfg]
       ,A.[Mfg Part Number]=B.[Mfg Part Number]
 from [Acctg].[dbo].[Co$AgileItem] as A
 Inner Join [Acctg].[dbo].[Co$AgileOra] as B
    on A.[No.] = b.[No.]
 

assuming that [No.] is the primary key of both tables...


0
 

Author Comment

by:mtnbuzz
Comment Utility
Ran the code:

update [acctg].[dbo].[co$AgileItem]
  set [acctg].[dbo].[co$AgileItem].[Mfg] = [acctg].[dbo].[co$AgileOra].[Mfg]
       ,[acctg].[dbo].[co$AgileItem].[Mfg Part Number] = [acctg].[dbo].[co$AgileOra].[Mfg Part Number]
 from [acctg].[dbo].[co$AgileItem] as A
 Inner Join [acctg].[dbo].[co$AgileOra] as B
    on A.[No.] = b.[No.]
 
and got this error

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'acctg.dbo.co$AgileOra' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'acctg.dbo.co$AgileOra' does not match with a table name or alias name used in the query.

But I do have a table named acctg.dbo.co$AgileOra?
0
 
LVL 21

Accepted Solution

by:
mastoo earned 500 total points
Comment Utility
update [acctg].[dbo].[co$AgileItem]
  set [acctg].[dbo].[co$AgileItem].[Mfg] = [acctg].[dbo].[co$AgileOra].[Mfg]
       ,[acctg].[dbo].[co$AgileItem].[Mfg Part Number] = [acctg].[dbo].[co$AgileOra].[Mfg Part Number]
 from [acctg].[dbo].[co$AgileItem]
 Inner Join [acctg].[dbo].[co$AgileOra]
    on [acctg].[dbo].[co$AgileItem].[No.] = [acctg].[dbo].[co$AgileOra].[No.]
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
You're defining aliases, then not using them except in the ON clause. This will do it:

update A
  set [Mfg] = B.[Mfg]
       , [Mfg Part Number] = B.[Mfg Part Number]
 from [acctg].[dbo].[co$AgileItem] as A
 Inner Join [acctg].[dbo].[co$AgileOra] as B
    on A.[No.] = b.[No.]
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now