How to update column in one table from other table without common key

Question

This is probably very basic but I dont know what to search for.

Table1:

someid value
1      0
2      0
3      0

Table2:

someid value
9      1
10     2
11     3

I want to update Table1.value with Table2.value values row by row, no common key, no where clause just table1.value row1 = table2.value row1 etc. Like a horizontal union.

So Table1 should be:

someid value
1      1
2      2
3      3

I try:

update table1
set value = table2.value
from table2

But all values are from first row in table2:

1   1
2   1
3   1

Answer 1

You can use row_number():

update table1 
set value = t2.value
from (
  select id, value, row_number() OVER (ORDER BY id) AS n from table1
) t inner join (
  select id, value, row_number() OVER (ORDER BY id) AS n from table2
) t2 on t.n = t2.n
where t.id = table1.id

See the demo.
Results for Table1:

> id | value
> -: | ----:
>  1 |     1
>  2 |     2
>  3 |     3

If you are sure that the ids in table1 are consecutive with no gaps and start from 1, then the query can be simplified like this:

update table1 
set value = t2.value
from (
  select id, value, row_number() OVER (ORDER BY id) AS n from table2
) t2 
where t2.n = table1.id

See the demo.