Subquery in an Update statement. Why doesn't this work?

  • Thread starter Thread starter Jarek
  • Start date Start date
J

Jarek

I'm trying to update a field in myTable1 with a value from myTable2 with a
statement like that:

Update myTable1 Set myTable1.myField=(select myTable2.myField from myTable2
Where myTable2.Table1Id=myTable1.Id) Where ...

But Access doesn't allow it. I get a message that the operation has to use
an updatable subquery (I don't have the English version of Access, so I only
can translate the error message rather than quoting it)

This error only happens if I use a subquery. If I change the subquery in the
above example to a simple number e.g. 100, then the statement will be
executed properly.
 
Jarek said:
I'm trying to update a field in myTable1 with a value from myTable2 with a
statement like that:

Update myTable1 Set myTable1.myField=(select myTable2.myField from myTable2
Where myTable2.Table1Id=myTable1.Id) Where ...

But Access doesn't allow it. I get a message that the operation has to use
an updatable subquery (I don't have the English version of Access, so I only
can translate the error message rather than quoting it)

This error only happens if I use a subquery. If I change the subquery in the
above example to a simple number e.g. 100, then the statement will be
executed properly.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yeah,... Access UPDATE queries are buggers like that. You could try
this:

UPDATE myTable1 As A INNER JOIN myTable2 As B ON A.ID = B.ID
SET A.myField = B.myField
WHERE <criteria for JOINed tables>

You can also use a Domain Aggregate function (e.g.: DLookup()) in place
of your subquery.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOEmYechKqOuFEgEQLYqwCgp2GFz0gxg1ZaeP/3Fbaaa9t3tEcAn3K8
UvvYSN4sg3v29b7RzAPB47pv
=6gLe
-----END PGP SIGNATURE-----
 
Update myTable1 Set myTable1.myField=(select myTable2.myField from myTable2
Where myTable2.Table1Id=myTable1.Id) Where ...
Click to expand...

A Join will pbobably work better:

UPDATE MyTable1 INNER JOIN MyTable2
ON MyTable1.ID = MyTable2.ID
SET MyTable1.MyField = MyTable2.MyField
WHERE...

There must be a unique Index on MyTable2.ID in order for this to work,
otherwise it would be ambiguous which value you're using to do the
update.
 
Back
Top