UPDATE SET xxx=SELECT Help

[Patrick Drouin]

Hello everyone,

I'm quite new to ACCESS and I have a fairly good knowledge of SQL under
Oracle. I'm trying to run the following query without any luck:

UPDATE M1L
SET M1L.Code_err = (SELECT M1.Code_err
FROM table_temp M1
WHERE M1L.Forme=M1.Forme
AND M1L.PdeD=M1.PdeD
AND M1.Code_Err is not null);
Access gives me a strange error message about not being to update something
with something (I run a French Office) and it's too late for a good
translation. I'm puzzled. Does Access support that kind of query? If not, is
there a work around?

Thanks,
Patrick

[Wayne Morgan]

The closest thing I could come up with that appears to be what you have is

UPDATE M1L INNER JOIN M1 ON (M1L.PdeD = M1.PdeD) AND (M1L.Forme = M1.Forme)
SET M1L.Code_err = [M1].[Code_err]
WHERE ((Not (M1.Code_err) Is Null));

The only problem I see with this is that if M1 has more than one value that meets all 3
criteria, the last one that the query comes to will be the one finally stored in M1L.

Example:
M1.PdeD = A, M1.Forme = B, M1.Code_err = 1
M1.PdeD = A, M1.Forme = B, M1.Code_err = 2

M1L.PdeD = A, M1L.Forme = B, M1L.Code_err = 3

When done, do you want 3 to become 1 or 2? It will depend on the sort order in the query,
the one it comes to last will be the new value in place of 3. The data in your table may
be such that this isn't a problem.

--
Wayne Morgan
"Patrick Drouin" <pa************@uREMOVE-THISmontreal.ca> wrote in message
news:fB*********************@charlie.risq.qc.ca...

Hello everyone,

I'm quite new to ACCESS and I have a fairly good knowledge of SQL under
Oracle. I'm trying to run the following query without any luck:

UPDATE M1L
SET M1L.Code_err = (SELECT M1.Code_err
FROM table_temp M1
WHERE M1L.Forme=M1.Forme
AND M1L.PdeD=M1.PdeD
AND M1.Code_Err is not null);
Access gives me a strange error message about not being to update something
with something (I run a French Office) and it's too late for a good
translation. I'm puzzled. Does Access support that kind of query? If not, is
there a work around?

Thanks,
Patrick

[Patrick Drouin]

Hello Wayne,

"Wayne Morgan" <co***************************@hotmail.com> a écrit dans le
message de news:iU*****************@newssvr16.news.prodigy.co m...

The closest thing I could come up with that appears to be what you have is

UPDATE M1L INNER JOIN M1 ON (M1L.PdeD = M1.PdeD) AND (M1L.Forme = M1.Forme) SET M1L.Code_err = [M1].[Code_err]
WHERE ((Not (M1.Code_err) Is Null));

The only problem I see with this is that if M1 has more than one value that meets all 3 criteria, the last one that the query comes to will be the one finally

stored in M1L.

Thanks, I'll give that a try and see if it works. I don't really mind if
more than one value matches since if I get a value the record will be
scraped regardless of the content. I didn't think of using INNER JOIN to do
that, not a bad idea at all. I like subqueries a lot better though. ;o)

Ciao and thanks again!
P