Operation must use an updateable query

[Guest]

The scenario goes like this

I have a linked table and a local table created in the database. I want to
update values in two columns in the local table based on its match in the
linked table. The common column between the two tables has unique row in the
local table and multiple rows in the linked table.

The query used for this purpose is as follows

UPDATE Event_tracker, (SELECT DISTINCT Talon_Staging_Table.MWBNBR,
Talon_Staging_Table.TALONEXTRACTDATETIME
FROM Talon_Staging_Table) as vk
SET Event_tracker.Talstg_event_status = "P", Event_tracker.Talstg_Post_date
=
Format(Mid(vk!TALONExtractdatetime,5,2)+"/"+Mid(vk!TALONExtractdatetime,7,2)+"/"+Mid(vkt!TALONExtractdatetime,1,4)+"
"+Mid(vk!TALONExtractdatetime,10,2)+":"+Mid(vk!TALONExtractdatetime,12,2)+":"+Mid(vk!TALONExtractdatetime,14,2),"mm/dd/yyyy hh:nn:ss AM/PM")
WHERE Event_tracker.Document_Number=vk!MWBNBR And
Event_Tracker.LV_Event_Status="P";

-where Event_tracker is the local table and Talon_staging_table is the
linked table. The columns being updated are Talstg event status which is
hardcoded as "P"
and the other one is a date which is in a different format ( hence the
format changes text to the date)

The reason I am using following query in the update statement is to get the
distinct values is as a result of the error " Operation must use an
updateable query". But the result doesnt change

(SELECT DISTINCT Talon_Staging_Table.MWBNBR,
Talon_Staging_Table.TALONEXTRACTDATETIME
FROM Talon_Staging_Table) as vk

The same thing works if I use a seperate table having the distinct values by
running the above query seperately and then I use a update on them.

Can someone guide me as to how do I fix this.

Thanks

Venky

 

[Jeff Boyce]

SELECT DISTINCT precludes updating -- how would Access know which one of the
underlying records to update?

Jeff Boyce
<Access MVP>

 

[MGFoster]

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

Your date/time value in the linked table resembles the ISO date/time
format: yyyymmddThhnnss. Are you sure this is stored as a string? If
it is a valid date/time data type Access can read it as a date/time data
type w/out your conversion function.

As Mr. Boyce implies, how will you determine which row in the "multiple
rows...linked table" will be the row that is used in the update? Do the
multiple rows all have the same value in the TalonExtractDateTime
column? Is the column MWBNBR unique?

If so, perhaps this (assumes TalonExtractDateTime is a string):

UPDATE Event_tracker
SET Talstg_Post_date =
(SELECT TOP 1
CDate(Format(Left(TalonExtractDateTime,8)"0000-00-00")) +
CDate(Format(Right(TalonExtractDateTime,6),"00:00:00"))
FROM Talon_Staging_Table
WHERE MWBNBR = Event_Tracker.Document_Number)
WHERE LV_Event_Status="P";

You don't have to set the Status to P when it is already P.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQlYl/YechKqOuFEgEQK1IwCggp008Io7K/yje/gsadu+MJ588ggAn28l
DwR9j/tgBJyjGtGOWwA1+Lgl
=ffBf
-----END PGP SIGNATURE-----