Update from aggregate in same table if aggregate value wrong - SQL Server/Oracle/Firebird

Question

I have a table with grouped tasks:

• tt_plan_task_id is the id
• records with tt_plantype=1 represent 'groups'
• tasks in/under a group have a tt_group_id pointing to the tt_plan_task_id
• there are tasks that don't belong to a group (tt_group_id is null)
• groups nest multiple levels

I need to fix (update) the tt_fromdate field values for the group records if they do not match the min(tt_fromdate) from the underlying tasks (they always have a value).

To fix them all I could do

update tt_plan_task g
set tt_fromdate=
 (select min(t.tt_fromdate) from tt_plan_task t
  where (t.tt_group_id=g.tt_plan_task_id))
where (g.tt_plantype=1)

This statement avoids the UPDATE FROM syntax that I see in many (SQL server) answers - Firebird does not support that.

There are 2 complications

1. I want to do the update only if g.tt_fromdate <> min(t.tt_fromdate), so I would have to add a reference to min(tt_fromdate) to the outer where.
   I tried using an alias for the aggregate and referencing that but that got me nowhere (syntax errors)

2. SQL Server does not like the table alias in the update, but solutions like these use the UPDATE FROM syntax again ;-( How do I work around that then?

How do I tie 1. and 2. into my update statement so that it works?
As noted in the title, this needs to execute in SQL Server, Oracle, and Firebird

Note: Since groups can contain groups, the update should ideally be executed 'from the bottom up', i.e. deepest groups first.
But since this is just a rough correction for a corrupt database, doing one 'lineair' pass over all groups is good enough.

Answer 1

To get around SQL Server's non-standard way for update table aliases, simply don't use any.

As to using the aggregate result in both the SET clause and the WHERE clause, I suppose the only way all DBMS work with, is to write the aggregation query twice.

update tt_plan_task
set tt_fromdate =
(
  select min(t.tt_fromdate) 
  from tt_plan_task t
  where t.tt_group_id = tt_plan_task.tt_plan_task_id
)
where (tt_plantype=1)
and 
(
  tt_fromdate <>
  (
    select min(t.tt_fromdate) 
    from tt_plan_task t
    where t.tt_group_id = tt_plan_task.tt_plan_task_id
  )
);