Updating SQL table with data from another

[Jenns]

I'm trying to write a query to update a field in a SQL.7 table with data in another table where the unique identifiers are equal.

I tried doing this in Access and then checking the sql behind the query. The query works but the sql didn't work in SQL.7.

example: UPDATE aos INNER JOIN goodsic ON aos.duns = goodsic.duns SET aos.sic = goodsic.sic

The error I get is invalid syntax near the keyword "Inner".

The other code I tried was:

UPDATE aos
SET aos.sic =
(SELECT goodsic.sic
FROM goodsic INNER JOIN
aos ON goodsic.duns = aos.duns)

The error I get is that the subquery returns more than 1 value.

Can someone please help? I thought I was on the right track, but I'm stuck.

Thanks
Jenn

 

[TomSark]

The error in the second query seems to be related to the fact that you are returning more than one value from the select.

As for the first update query, you need a FROM and you need to use a table alias as in:

UPDATE a
SET a.sic = g.sic
FROM aos a
INNER JOIN goodsic g ON a.duns = g.duns

I hope this helps (and I haven't had my caffeine yet )...

Tom

 

[Jenns]

Thanks Tom!
The alias thing did it.

Jenn