Обсуждение: Help with a complex Update
I have a table of users and a table of user_actions. I'd like to have a first_action column in the user table. How would I go about updating that info. I'd immagine it'd be something like this: update user_info set first_action = (select min(action_timestamp) from action_info where user_id = ?? current user id ??); Is this possible or do I have to make an external for loop? Orion
On Fri, Nov 02, 2001 at 03:15:36PM -0800, Orion wrote: > I have a table of users and a table of user_actions. > I'd like to have a first_action column in the user table. > > How would I go about updating that info. > > I'd immagine it'd be something like this: > > update user_info set first_action = (select min(action_timestamp) from > action_info where user_id = ?? current user id ??); Guessing at your table schema, but I think you want: update user_info set first_action = min(action_timestamp) from action_info where action_info.user_id = user_id; Ross
Orion, > update user_info set first_action = (select min(action_timestamp) > from > action_info where user_id = ?? current user id ??); Close. You need to use a subselect in the FROM clause: UPDATE user_info SET first_action = min_action FROM (select user_id, min(action_timestamp) as min_action FROM action_info WHERE user_id = $user_variable GROUPBY user_id) ma WHERE user_info.user_id = ma.user_id; -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco