Why Can't I Use Select ... for Update with Aggregate Functions

Why can't I use SELECT ... FOR UPDATE with aggregate functions?

The syntax select . . . for update locks records in a table to prepare for an update. When you do an aggregation, the result set no longer refers to the original rows.

In other words, there are no records in the database to update. There is just a temporary result set.

Aggregate function in an SQL update query?

UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
   from table2
  group by field3) as t2
on t2.field3 = t1.field3  

SQL Update statement using aggregate function causing error

I would recommend:

update ft
    set final_cost = t1.down_payment 
    from final_table ft outer apply
         (select top 1 t1.*
          from table_1 t1
          where t1.car_type = ft.car_type and
                t1.date_purchased <= ft.date
          order by t1.date_purchased desc
         ) t1;

FOR UPDATE is not allowed with aggregate functions in PostgreSQL

You can do FOR UPDATE within a subquery:

rate_count := COUNT(id) 
              FROM (
                SELECT id FROM job
                WHERE last_processed_at >= ? FOR UPDATE
                ) a;

You can also do this whole thing in a single query:

UPDATE job SET state='processing'
WHERE id IN (
  SELECT id FROM job
  WHERE state='pending'
  LIMIT (SELECT GREATEST(0, rate_limit - COUNT(id))
         FROM (SELECT id FROM job
               WHERE last_processed_at >= ? FOR UPDATE) a
        )
)

Select max value with for update nowait in oracle

The syntax select . . . for update locks records in a table to prepare for an update. When you do an aggregation, the result set no longer refers to the original rows.

In other words, there are no records in the database to update. There is just a temporary result set.

Aggregate update-from-select won't update null values

You can use a correlated subquery:

UPDATE FTT_REGISTRY_REPORT REP
    SET COUNT_491 = (SELECT COUNT(*)
                     FROM FTT_TX_491 OP
                     WHERE REP.SOURCE = SUBQUERY.SOURCE AND
                           REP.REGISTRY_ID = SUBQUERY.REGISTRY_ID
                    );

The subquery will always return a value. The count will be zero if no rows match.

Another method would be to define the column as NOT NULL DEFAULT 0. This will create the column with zeros and then you can just update the rows with other values.

---

Related Topics