how to update table1 from table2 ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

how to update table1 from table2 ?

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2005-12-25 : 13:39:43

Hello!

I have a problem to update a table1 from a table2! In Access the following sql-query works fine... but not in SQL-SERVER (2005,Express)!

UPDATE dbo_Item, dbo_Element_Article
SET dbo_Item.Element_ID = dbo_Element_Article.Element_Id
WHERE (dbo_Item.Artcoar=dbo_Element_Article.ARTCOAR) And (dbo_Item.Artpere=dbo_Element_Article.ARTPERE);

Anybody could help me out ?

Thank you very much !

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-12-25 : 15:46:55

UPDATE dbo_Item
SET dbo_Item.Element_ID = dbo_Element_Article.Element_Id
WHERE (dbo_Item.Artcoar=dbo_Element_Article.ARTCOAR) And (dbo_Item.Artpere=dbo_Element_Article.ARTPERE)

fabianus76
Posting Yak Master

191 Posts

Posted - 2005-12-25 : 17:10:39

Hello Cshah1,

thank you very much for your help!

I tried your query but get this error :
The multi-part identifier "dbo.Element_Article.ARTCOAR" could not be bound.

But I had an idea... and it works :-) :
UPDATE dbo.Item SET dbo.Item.Element_ID =
(SELECT dbo.Element_Article.Element_Id
FROM dbo.Element_Article
WHERE dbo.Item.Artcoar=dbo.Element_Article.ARTCOAR And dbo.Item.Artpere=dbo.Element_Article.ARTPERE)

Thank you very much!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-26 : 00:40:52

Try this also

UPDATE I SET dbo.Item.Element_ID =E.Element_Id FROM dbo.Item I inner join dbo.Element_Article E
on I.Artcoar=E.ARTCOAR where I.Artpere=E.ARTPERE

Madhivanan

Failing to plan is Planning to fail

fabianus76
Posting Yak Master

191 Posts

Posted - 2005-12-26 : 09:41:37

Hi Madhivana,

I get the following error :
The multi-part identifier "dbo.Item.Element_ID" could not be bound.

I changed it to this :
UPDATE I SET I.Element_ID =E.Element_Id FROM dbo.Item I inner join dbo.Element_Article E
on I.Artcoar=E.ARTCOAR where I.Artpere=E.ARTPERE

But still, it just reports :
Executed OK, 0 rows affected (0,28 sec)

But there are about 800 rows.

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources