updating column in same table based on another column, Postgres

Question

I would like to update a column that has missing data based on matches within the same table, for this example, no locations came through on 01/02/20, so based on matching the campaign and ID, update this data.

Sample table:

PersonID| date_serve | campagign | location
--------+----------+--------+---------+
1       | 01/01/2020 |  email    | Sydney   |    
1       | 01/02/2020 |  email    |          | 
2       | 01/01/2020 |  email    | London   | 
2       | 01/02/2020 |  email    |          | 
2       | 01/01/2020 |  text     | New York | 
2       | 01/02/2020 |  text     |          |

Expected Output:

PersonID| date_serve | campagign | location
--------+----------+--------+---------+
1       | 01/01/2020 |  email    | Sydney   |    
1       | 01/02/2020 |  email    | Sydney   | 
2       | 01/01/2020 |  email    | London   | 
2       | 01/02/2020 |  email    | London   | 
2       | 01/01/2020 |  text     | New York | 
2       | 01/02/2020 |  text     | New York |

Current script I've developed but returns locations for all IDs to become Sydney:

UPDATE Persons ps
SET location = ps2.location
FROM Persons ps1, Persons ps2
WHERE ps1.PersonID = ps2.PersonID AND ps1.location = '' 
AND ps1.campaign = ps2.campaign
RETURNING ps.PersonID, ps.location, ps.Date_served

Also, a SQL fiddle for the above. http://sqlfiddle.com/#!17/0feac/1

Thanks in advance,

Gordon Linoff · Accepted Answer · 2020-06-23 18:32:16Z

2

You have too many references to the table:

UPDATE Persons ps
    SET location = ps2.location
    FROM Persons ps2
    WHERE ps.PersonID = ps2.PersonID AND
          ps.location = '' AND
          ps.campaign = ps2.campaign
    RETURNING ps.PersonID, ps.location, ps.Date_served;

answered Jun 23, 2020 at 18:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

The Impaler · Accepted Answer · 2020-06-23 18:34:27Z

1

You can use a subquery:

update persons p
set location = (
  select q.location
  from persons q 
  where q.personid = p.personid and q.date_server = '2020-01-01'
)
where date_serve = '2020-01-02'

answered Jun 23, 2020 at 18:34

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Collectives™ on Stack Overflow

updating column in same table based on another column, Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related