Update all fields from a table with the values of a related table

[Fons Roelandt]

Heelo,

I have to Update all fields from a table with the values of a related table,
i've tried some querys i found on the internet, but nothing seems to word, i
even tried to lookup the value using dlookup, but even that doesnt seem to
word in a update query.

The query that i think should work is this one:

UPDATE tblOrderLines AS tblO
SET tblO.olArtCode = (
SELECT tblA.artArtCode
FROM tblArticles AS tblA
WHERE (tblA.artArticl eID = tblO.olArticleI D)
);

But it doen't work, the field olArtCode stays empty after running this
query, i don't get any errors while running the query so i guess the syntax
must be right...

Can anyone tell me what i'm doing wrong?

TNX! Fons

[Marc]

Re: Update all fields from a table with the values of a related table

Fons,

it shouldn't be too difficult.
if the relation to the related table can be made y linking to a
primary key in the linked table in a select-query, you can select the
fields to update in the query-editor, convert the query to a
update-query type ('query bijwerken' option) through the menu and
then select the field from the related table into the 'wijzigen in'
row. The sql will be created for you. The joined table-pair will be
updatable when the right indexes are defined for the join-fields. So
it won't work in most cases when the tables are designed the right
way.

Marc

"Fons Roelandt" <f.roelandt@zee landnet.nl> wrote in message news:<3f9fae12$ 0$810$fb624cd1@ news1.zeelandne t.nl>...[color=blue]
> Heelo,
>
> I have to Update all fields from a table with the values of a related table,
> i've tried some querys i found on the internet, but nothing seems to word, i
> even tried to lookup the value using dlookup, but even that doesnt seem to
> word in a update query.
>
> The query that i think should work is this one:
>
> UPDATE tblOrderLines AS tblO
> SET tblO.olArtCode = (
> SELECT tblA.artArtCode
> FROM tblArticles AS tblA
> WHERE (tblA.artArticl eID = tblO.olArticleI D)
> );
>
> But it doen't work, the field olArtCode stays empty after running this
> query, i don't get any errors while running the query so i guess the syntax
> must be right...
>
> Can anyone tell me what i'm doing wrong?
>
> TNX! Fons[/color]

[Fons Roelandt]

Re: Update all fields from a table with the values of a related table

Thanks Marc,

Your solution worked!