Update using an inner 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

General SQL Server Forums

New to SQL Server Programming

Update using an inner join

Author

Topic

Carlos Vela
Starting Member

4 Posts

Posted - 2006-07-06 : 16:21:19

I am trying to update a file based on data retrieved from a join and performing a calculation prior to updationg my result field. I end up with "Column qualifier or table B undefined. "

can anyone see what my problem is?

update a
set a.yr2004 = (b.smal + b.smat) * a.qtypre
from commodityf a inner join itmrvb b
on a.i@stid = b.stid
and a.cinbr = b.itnbr

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-06 : 16:31:50

This column name doesn't look correct: i@stid

If that is the correct column name, try enclosing it in brackets:

on a.[i@stid] = b.stid

CODO ERGO SUM

Carlos Vela
Starting Member

4 Posts

Posted - 2006-07-06 : 16:36:22

That is the correct column name and enclosing gives me same error. The error code seems to point to the use of (b.smal + b.smat) * a.qtypre where table B is undefined

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-06 : 16:53:39

update a
set yr2004 = (b.smal + b.smat) * a.qtypre
from commodityf a
inner join itmrvb b
on a.[i@stid] = b.stid and a.cinbr = b.itnbr

The part that I changed was in the set. You do not specify an alias on the column to set.

Tara Kizer
aka tduggan

Carlos Vela
Starting Member

4 Posts

Posted - 2006-07-06 : 17:01:55

Nope - still get same error - This is driving me nuts

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-06 : 17:03:42

Are you using Microsoft SQL Server or some other database management system?

Tara Kizer
aka tduggan

Carlos Vela
Starting Member

4 Posts

Posted - 2006-07-06 : 17:05:29

Using Sql400 on an Iseries

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-06 : 17:07:10

The query that I posted works fine in SQL Server. Since you are not using SQL Server, you'll need to post your question in a forum that supports Sql400 on iSeries. SQLTeam.com is primarily a Microsoft SQL Server site. You could try out dbforums.com (if their site is up).

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources