CODEWITHSUNDEEP
mysql
Colin
Colin

Reputation: 10820

Update table based on aggregate of other column in same table

I have a table with three columns right now that looks something like this:

A     B     C
1     a     NULL
2     a     NULL
3     a     foo
4     b     NULL
5     b     NULL
6     c     bar
7     c     NULL

and I want to add a column D that will look like this:

A     B     C     D
1     a     NULL  foo
2     a     NULL  foo
3     a     foo   foo
4     b     NULL  NULL
5     b     NULL  NULL
6     c     bar   bar
7     c     NULL  bar

In other words, D takes a non-null value, if there is one, from C when grouped by B. How can I do this in SQL? If it gets too complicated I'd be more comfortable just exporting the tables to a text file, writing a python script to figure out D, and then adding it to the table.

Upvotes: 0

Views: 78

Answers (1)

RuslanN
RuslanN

Reputation: 408

Here you are:

UPDATE Table n
    INNER JOIN
Table m ON n.B = m.B 
SET 
n.D = m.c
WHERE
m.C IS NOT NULL;

Upvotes: 1

Related Questions