Update columns with values from another column

0
votes

I have a Sqlite table where there are rows with the same values in ColumnA but different values in ColumnB. The below query gives me several hundred rows.

select ColumnA from MyTable Group by ColumnA having Count(*) > 1

My requirement is that for rows with identical ColumnA values, whenever there's a value in ColumnC in any row, populate ColumnC values in all the rows. Below is the input and output.

Can someone help?

+--------------------------+
| +------+-------+------+  |
+--------------------------+
| |ColumnA|ColumnB|ColumnC |
| +------+-------+------+  |
| |C2     |Val1   |        |
| +------+-------+------+  |
| |C2     |Val2   |P       |
| +------+-------+------+  |
| |B2     |Val3   |Q       |
| +------+-------+------+  |
| |B2     |Val4   |        |
| +------+-------+------+  |
+--------------------------+

Output:

+---------------------------------------------------+
|              +------+-------+------+              |
+---------------------------------------------------+
| |ColumnA|ColumnB|ColumnC                          |
| +------+-------+------+                           |
| |C2     |Val1   |P        //add P to this row     |
| +------+-------+------+                           |
| |C2     |Val2   |P                                |
|                                                   |
| +------+-------+------+                           |
| |B2     |Val3   |Q                                |
| +------+-------+------+                           |
| |B2     |Val4   |Q          //add Q to this row   |
| +------+-------+------+                           |
+---------------------------------------------------+
1
sqlsqlite

1 Answers

2
votes

Does this do what you want? It uses a correlated subquery to update the values:

update mytable
    set columnc = (select max(columnC) from mytable t2 where t2.columnA = mytable.columnA)
    where columnC is null;