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 Answer1
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;
Sign up to request clarification or add additional context in comments.
1 Comment
Aqua267
That's exactly what I wanted. Didn't realize max(columnC) could do the trick even for text columns. Thanks much!
Explore related questions
See similar questions with these tags.
