CODEWITHSUNDEEP
postgresqlpostgresql-9.5
leemour
leemour

Reputation: 12013

Update using subquery sets same value for all records

I'm trying to calculate the weight of each record based on the value of a column (updated_at). When I run the following query:

UPDATE buyers
SET weight = RankedRecords.rank / (RankedRecords.count + 1.0)
FROM (
  SELECT
    id,
    RANK() OVER (
      PARTITION BY board_list_id ORDER BY 'updated_at' ASC
      ) AS rank,
    COUNT(id) OVER (PARTITION BY board_list_id) AS count
  FROM buyers
) RankedRecords
WHERE buyers.id = RankedRecords.id

All records with the same board_list_id get their weight updated to the same value. While I expect all weight values to be different and depend on rank.
Running just the subquery produces correct results (each record has different rank). But updating doesn't work as expected.
What should I change?

Upvotes: 0

Views: 68

Answers (1)

joanolo
joanolo

Reputation: 6328

You have a very subtle mistake in your query. Try this instead:

UPDATE 
    buyers
SET 
    weight = RankedRecords.rank / (RankedRecords.count + 1.0)
FROM 
(
    SELECT
        id,
        rank()    OVER (PARTITION BY board_list_id ORDER BY updated_at ASC) AS rank,
        count(id) OVER (PARTITION BY board_list_id) AS count
    FROM buyers
) RankedRecords
WHERE 
    buyers.id = RankedRecords.id ;

Your litle mistake: ORDER BY 'updated_at' is just ORDER BY 'constant-text'. If you want to refer to the column, you either use "updated_at" (with double quotes) or updated_at (without them, because the name of your column is just ASCII lowercase chars).

Tried with:

CREATE TABLE buyers
(
    id integer not null primary key,
    board_list_id integer not null,
    updated_at timestamp not null default now(),
    weight double precision
) ;
INSERT INTO buyers (id, board_list_id, updated_at) 
VALUES 
    (1, 1, '2017-01-09'),
    (2, 1, '2017-01-10'),
    (3, 1, '2017-01-11'),
    (4, 1, '2017-01-12'),
    (5, 2, '2017-01-09'),
    (6, 2, '2017-01-10'),
    (7, 2, '2017-01-11'),
    (8, 1, '2017-01-12') ;

The result of the previous UPDATE (with a RETURNING * clause) would be:

|----+---------------+---------------------+--------+----+------+-------|
| id | board_list_id |     updated_at      | weight | id | rank | count |
|----+---------------+---------------------+--------+----+------+-------|
| 1  |       1       | 2017-01-09 00:00:00 | 0.1667 | 1  |  1   |   5   |
|----+---------------+---------------------+--------+----+------+-------|
| 2  |       1       | 2017-01-10 00:00:00 | 0.3333 | 2  |  2   |   5   |
|----+---------------+---------------------+--------+----+------+-------|
| 3  |       1       | 2017-01-11 00:00:00 | 0.5    | 3  |  3   |   5   |
|----+---------------+---------------------+--------+----+------+-------|
| 8  |       1       | 2017-01-12 00:00:00 | 0.6667 | 8  |  4   |   5   |
|----+---------------+---------------------+--------+----+------+-------|
| 4  |       1       | 2017-01-12 00:00:00 | 0.6667 | 4  |  4   |   5   |
|----+---------------+---------------------+--------+----+------+-------|
| 5  |       2       | 2017-01-09 00:00:00 | 0.25   | 5  |  1   |   3   |
|----+---------------+---------------------+--------+----+------+-------|
| 6  |       2       | 2017-01-10 00:00:00 | 0.5    | 6  |  2   |   3   |
|----+---------------+---------------------+--------+----+------+-------|
| 7  |       2       | 2017-01-11 00:00:00 | 0.75   | 7  |  3   |   3   |
|----+---------------+---------------------+--------+----+------+-------|

Upvotes: 1

Related Questions