Operation must use an updatable query

  • Thread starter Thread starter Serge Myrand
  • Start date Start date
S

Serge Myrand

Hi,

The following query raise an error and it should not. How to workaround
it?


UPDATE MACHINE_SHIFT_DTL AS msd SET msd.GROUP_ID =
(SELECT shift.GROUP_ID
FROM SHIFT
WHERE shift.SHIFT_ABREV =
msd.SHIFT_ABREV)
WHERE EXISTS
(SELECT shift.GROUP_ID
FROM SHIFT
WHERE shift.SHIFT_ABREV = msd.SHIFT_ABREV);


thank you in advance
serge
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you mean this is a JET query? If so, try this:

UPDATE MACHINE_SHIFT_DTL As MSD
SET GROUP_ID = DLookup("Group_ID","Shift","SHIFT_ABREV=msd.SHIFT_ABREV")
WHERE EXISTS (SELECT * FROM SHIFT
WHERE SHIFT_ABREV = msd.SHIFT_ABREV)

Or, this one:

UPDATE MACHINE_SHIFT_DTL As MSD INNER JOIN Shift As S
ON MSD.Shift_abrev=S.Shift_abrev
SET MSD.GROUP_ID = S.Group_ID

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

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

iQA/AwUBQX/jgIechKqOuFEgEQJR+gCdHdc7U9o+Fb1diW5zz71ZhfC84hAAoKHt
cMBxUDBP8cJ+ywDg0v9xGMtm
=zT5D
-----END PGP SIGNATURE-----
 
The second one does the job thank's
serge
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you mean this is a JET query? If so, try this:

UPDATE MACHINE_SHIFT_DTL As MSD
SET GROUP_ID = DLookup("Group_ID","Shift","SHIFT_ABREV=msd.SHIFT_ABREV")
WHERE EXISTS (SELECT * FROM SHIFT
WHERE SHIFT_ABREV = msd.SHIFT_ABREV)

Or, this one:

UPDATE MACHINE_SHIFT_DTL As MSD INNER JOIN Shift As S
ON MSD.Shift_abrev=S.Shift_abrev
SET MSD.GROUP_ID = S.Group_ID

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

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

iQA/AwUBQX/jgIechKqOuFEgEQJR+gCdHdc7U9o+Fb1diW5zz71ZhfC84hAAoKHt
cMBxUDBP8cJ+ywDg0v9xGMtm
=zT5D
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top