How to replace a column with another column from another table?

Question

I have a database stored in a GridDB container. The main table in the database contains a column with outdated data. I would like to replace the whole column with a new column from another table (with the same total number of rows). Is there any way I could do that with Python?

For example, the whole process looks thus:

-- old table

column_0 | column_1 | old_column
------------+-----------+------------
1344 | Max | 5263.42525
1345 | John | 1465.41234
1346 | Alex | 8773.12344
1347 | Matthew | 5489.23522
1348 | Mark | 9874.31423


-- replacement

col_0 | updated
---------+------------
4242 | 3553.42824
4243 | 8942.98731
4244 | 1424.36742
4245 | 7642.75352
4246 | 2844.92468


-- output

column_0 | column_1 | old_column
------------+-----------+------------
1344 | Max | 3553.42824
1345 | John | 8942.98731
1346 | Alex | 1424.36742
1347 | Matthew | 7642.75352
1348 | Mark | 2844.92468

I have tried to replace the values one by one but I want something faster and kind of automated.

Why in the replacement data , the col_0 values are different? — gotqn, Jan 26 '23 at 10:47
What you're trying to do is a join, but we're missing an information here. What is the criterion which links rows together in different tables ? Just their ordered position ? — davidriod, Jan 26 '23 at 11:58
Try to add your code, even if it is a small snippet, do not use the community as a problem solver. — Michal Miky Jankovský, Jan 26 '23 at 15:45

score 0 · Answer 1 · answered Jan 26 '23 at 12:07

What you are trying to do here is what is called a join in SQL. Assuming that rows between tables are matched according to their rank, you can join two subqueries with an rank() window function and then pick the wanted columns from both sets.

SELECT column_0, column_1, updated AS old_column
FROM
    (SELECT rank() over() r, * FROM old_table ORDER BY column_0) left
    JOIN (SELECT rank() over() r, * FROM replacement ORDER BY col_0) right
    ON left.r = right.r

Each sub-queries will add a new counter for each row with the rank function, rows will be matched together according to that rank value producing a row containing columns from both sub-queries. The top level will then only pick the wanted column with an appropriate AS clause for the wanted naming.

Each subquery

How to replace a column with another column from another table?

1 Answers1