update with two tables - 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 with two tables

Author

Topic

aguess
Starting Member

6 Posts

Posted - 2007-09-02 : 13:19:46

hi
i have two table and would like to update a one of them from the other.
i have to fill the column "num" of the CLIENT table , from the TAB1 table. with the condition CLIENT.id = TAB1.id

UPDATE [client] INNER JOIN nom_tab1 ON [client].id = tab1.id SET [client].num = [tab].[num];

but it didn't worked for me ,can u give me a hand ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-02 : 15:37:15

UPDATE c
SET c.num = [tab].[num]
FROM [client] AS c
INNER JOIN nom_tab1 AS [tab] ON [client].id = tab.id;

E 12°55'05.25"
N 56°04'39.16"

aguess
Starting Member

6 Posts

Posted - 2007-09-02 : 20:02:20

i had an error message that i can't resolve

Table 'c' does not exist or it not owned by you

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 20:15:28

quote:
Originally posted by aguess

i had an error message that i can't resolve
Table 'c' does not exist or it not owned by you

Just leave out the aliases:

UPDATE client
SET client.num = nom_tab1.num
FROM client
INNER JOIN nom_tab_1 ON client.id = nom_tab_1.id

------------------------
Future guru in the making.

aguess
Starting Member

6 Posts

Posted - 2007-09-02 : 20:29:06

i already tried it without the ALIAS but that don't work too,i got this message

line 1 , syntax error on '.' . the correct syntax is :
UPDATE table
[FROM table {,...}]
SET column = expression | NULL {,...}
[where search condition ]

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 21:02:06

Try it exactly like this:

UPDATE client
SET client.num = nom_tab1.num
FROM client
INNER JOIN nom_tab1 ON client.id = nom_tab1.id

There is no reason it shouldn't work in 2000 or 2005, what version of SQL are you using?

------------------------
Future guru in the making.

aguess
Starting Member

6 Posts

Posted - 2007-09-02 : 21:12:34

it still don't working .
i am using sql from ingres environement "isql".
i am trying to update an ingres table from an access table.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 21:26:33

Ahh well then.. that is beyond me. You probably need a forum on that product. For example:
http://community.ingres.com/forums/

------------------------
Future guru in the making.

aguess
Starting Member

6 Posts

Posted - 2007-09-03 : 02:27:39

why ?its not the same SQL ?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-03 : 10:31:29

SQL is different across platforms, they are not all purely conformed to ANSI-92 or any one standard so there are nuances that might be different. For that reason it may be best to consult with folks that use the same product.

------------------------
Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 10:57:52

quote:
Originally posted by Peso

UPDATE c
SET c.num = [tab].[num]
FROM [client] AS c
INNER JOIN nom_tab1 AS [tab] ON tab.id = c.id;
E 12°55'05.25"
N 56°04'39.16"

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-03 : 11:10:01

quote:
Originally posted by Peso

quote:
Originally posted by Peso

UPDATE c
SET c.num = [tab].[num]
FROM [client] AS c
INNER JOIN nom_tab1 AS [tab] ON tab.id = c.id;
E 12°55'05.25"
N 56°04'39.16"

E 12°55'05.25"
N 56°04'39.16"

Peso, apparently this won't work for him, he is using Ingres. It works when I try it.

------------------------
Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 11:58:57

Ok, which part of Microsoft SQL Server forum did he miss?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources