Sybase: SQL Update table based on another table

Question

So I have this sql query

select user_id, profile_id 
from user_groups join profile_groups on 
user_groups.group_id=profile_groups.group_id 
group by user_id, profile_id
having count(user_groups.group_id) = 
(select count(*) from profile_groups as pg where 
pg.profile_id=profile_groups.profile_id)
and count(profile_groups.group_id) = (select count(*) from user_groups as ug where 
ug.user_id=user_groups.user_id)
;

I wish to update the profile_id of my user table. I have the following

UPDATE user u
SET u.profile_id = t.profile_id
FROM (
    select user_id, profile_id 
    from user_groups join profile_groups on 
    user_groups.group_id=profile_groups.group_id 
    group by user_id, profile_id
    having count(user_groups.group_id) = 
    (select count(*) from profile_groups as pg where 
    pg.profile_id=profile_groups.profile_id)
    and count(profile_groups.group_id) = (select count(*) from user_groups as ug where 
    ug.user_id=user_groups.user_id)
    ) t where t.user_id = u.id

But it gives me an error saying that i cannot use a derived table in the from clause of a update or delete statement.

How would I go about doing this?

Cheers.

Answer 1

I ended up just creating a temp table. Inserting the rows and updated the fields via the temp table.

Worked like a charm and probably easier too.

There might be performance issues but given the size of my dataset and the fact that this query is only ever run once it shouldn't be too big of a problem.