Copy only the selected values form one table to another one with where clause in SQLite

Question

I have a table with duplicated rows (table: electricity_meters). And there is another table (table: power_consumptions) that tells me which row of the first table to keep.

Trying delete the duplicated rows in SQLite with CTE, but not working.

Here is what I am trying, with DB Browser for SQLite:

with CTE_table AS
(
select *, row_number() over (PARTITION by serial_number ORDER by consumed_value DESC) as row_num
from electricity_meters
left JOIN power_consumtions on electricity_meters.adressId=power_consumptions.adressId
)
delete from CTE_table
WHERE row_num > 1

And the result:

Execution finished with errors.
Result: no such column: row_num

serial_number is in the first table (electricity_meters), and the consumed_value is in the second table (power_consumtions).

Any suggestions or help would be great. Thanks.

Answer 1

I have a sqlite DB with a TABLE "TxData" and these two columns NxData and txSearchMonth both are integer types try adding a condition statement WHERE row_num = your value

UPDATE TxData SET NxData = txSearchMonth