Update one column with the results from another column?

[BlakeK]

I am having a problem creating an update query.
I swear I have done this before, but I have been racking my brain all day, and getting no where.

Basically, I want to update the value of one column returned in my select clause, with another column returned by the select clause.

Here is the select query which yields my results...

SELECT tbl_club_ads.club_id, tbl_club_ads.ad_id, tbl_club_ads.playback_date, min(tbl_server_logs.play_time) AS min_play_time
FROM tbl_server_logs, tbl_club_ads
WHERE tbl_club_ads.club_id = tbl_server_logs.club_id
AND tbl_club_ads.ad_id = tbl_server_logs.ad_id
AND tbl_club_ads.playback_date Is Null
GROUP BY tbl_club_ads.club_id, tbl_club_ads.ad_id

So, if tbl_club_ads had the following rows:
club_id ad_id playback_date
1_____1____Null
1_____2____Null

...and if tbl_server_logs had the following rows:
club_id ad_id play_time
1_____1____1/1/01
1_____1____3/1/01
1_____2____5/1/01
1_____2____9/1/01


Then the query results is a set of data like this:
club_id ad_id playback_date min_play_time
1_____1____Null_________1/1/01
1_____2____Null_________5/1/01

This is exactly what I need.
But, now I need to update the records in tbl_club_ads so that the playback_date field contains the minimum play_time date which was determined in the query.

I know that I could open the select query as a record set (either in a SQL cursor or through and ADO recordset) and go through record by record and update each one individually. But, I know there is a way to do this en mass.
But, for the life of me, I can't remember how. >:-<

If anyone can lend me a clue, point me in the right direction, or anything, I would be greatful.

 

[Tim1]

Hiya,

Not being quite sure which fields you want to update, the following is the general solution, you will need to adapt it to put in table and variable names as required:

UPDATE table_1
SETfield_1 = (SELECT MIN(t2.field_1)
FROM table_2 t2
WHERE table_1.key_field = t2.key_field
GROUP BY t2.key_field)

HTH

Tim