postgresql updating column with NULL from the same column with data

Question

Table name: myTable, where I need to update the values of A with Null from the same column

Group      Value
A            10
A            NULL
A            NULL
A            10
B            20
B            20
B            20
C            30

Expected:

Group      Value
A            10
A            10
A            10
A            10
B            20
B            20
B            20
C            30

MY query:

Update myTable
Set myTable.Age = b.Age
FROM myTable b
WHERE A.Age is Null

I am getting relation doesn't exist.

What am I doing wrong?

I suggest to join these tables in where clause, otherwise it would result in cartesian product. — Ivanka
– Ivanka, Commented Oct 22, 2018 at 18:36

Yevhen Bondar · Accepted Answer · 2018-10-22 18:35:15Z

2

Try this query

UPDATE myTable SET Age = 
    (SELECT b.Age 
     FROM myTable b 
     WHERE b.Group = myTable.Group AND  b.Age IS NOT NULL
     LIMIT 1) 
WHERE Age IS NULL

answered Oct 22, 2018 at 18:35

Yevhen Bondar

4,7471 gold badge15 silver badges40 bronze badges

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

2 Comments

MrKarma4u Over a year ago

my only question here is why LIMIT 1

Yevhen Bondar Over a year ago

@user2934524 Because in your table could be several rows for Group = A. For example table contains these rows (A, 10), (A, 20). So, LIMIT 1 choose only one row with Value=10.

Gordon Linoff · Accepted Answer · 2018-10-22 22:31:10Z

1

I think a correlated subquery might be the way to go:

update myTable t
    set Age = (select t2.Age from mytable t2 where t2.name = t.name and t2.Age is not null)
    where t.Age is Null;

Note: This will generate an error if age is on more than one row of the original data for a given name.

If performance is an issue, you want an index on mytable(name, age).

Here is a db<>fiddle example.

edited Oct 22, 2018 at 22:31

answered Oct 22, 2018 at 18:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

MrKarma4u Over a year ago

I ran this and its been 10 mins and still running

MrKarma4u Over a year ago

IT completed after an hour but didnt update anything

Gordon Linoff Over a year ago

@user2934524 . . . The code works. I provided an example.

Collectives™ on Stack Overflow

postgresql updating column with NULL from the same column with data

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related