Update query - from 'Many' to 'One' Relationship

[TimGoff]

Hello,

The following query produces an error 'Operation must be an updateable query'. There are many records in dbo_media_audit to one in tbl_Bcst_t_media_root.

How can I get round this please?

Thanks in advance,
Tim

UPDATE tbl_Bcst_t_media_root, dbo_media_audit

SET tbl_Bcst_t_media_root.Activity_Status_Date =
MAX(dbo_media_audit.Audited_On)

WHERE tbl_Bcst_t_media_root.Current_Location = 'Non Standard Location' AND tbl_Bcst_t_media_root.media_sys_id = dbo_media_audit.media_sys_id

 

[Remou]

Hi
If there is only one record in media_sys_id why have you got the WHERE statement and the MAX stuff for that table?
Maybe try:
UPDATE tbl_Bcst_t_media_root, dbo_media_audit SET tbl_Bcst_t_media_root.Activity_Status_Date = [dbo_media_audit].[Audited_On];

 

[TimGoff]

Hi Remou,

There is one tbl_bcst_t_media_root.media_sys_id & many dbo_media_audit.media_sys_id

I would also need the WHERE clause to join the two tables?

 

[Remou]

Sorry, I did not read carefully enough (and cut and paste errors).

 

[TimGoff]

Based on other posts I've tried ...so no joy...

UPDATE tbl_Bcst_t_media_root AS Media_Root SET Media_Root.Activity_Status_Date = (SELECT Max(dbo_media_audit.Audited_On)
FROM
dbo_media_audit
WHERE
dbo_media_audit.media_sys_id = Media_root.media_sys_id);

 

[Remou]

This is the nearest I could get:
UPDATE tbl_Bcst_t_media_root SET tbl_Bcst_t_media_root.Activity_Status_Date = DMax("Audited_On","dbo_media_audit");

 

[TimGoff]

This is the SELECT...just how do you turn it into an UPDATE?

SELECT med_root.media_sys_id, Aud.Max_Aud_on
FROM tbl_bcst_t_media_root AS Med_Root,
(SELECT Max(dbo_media_audit.Audited_On) as Max_Aud_on,
media_sys_id
FROM dbo_media_audit
GROUP BY media_sys_id) AS Aud
WHERE Med_Root.media_sys_id = aud.media_sys_id

 

[Remou]

I think this says why you need to use DMax (but then there is my reading problem )

Update Query
thread701-609992