how to copy a column with another column related-CodePudding

I have two tables on my postgresql db like below.

[Table A]                                    [Table B]
Name            no                           Name             no
------          ------                       ------           ------
1. Adam         1                            1. Candy         null
2. Bob          2                            2. Evan          null          
3. Candy        3                            3. David         null
4. David        4                            4. Bob           null
5. Evan         5                            5. Adam          null

[Table B Name] has its foreign key related to [Table A Name].
[Table B no] has just created by alter table command.
What I want here is to fill out the [Table B no] column in accordance with [Table A no].

so the result must be below.

[Table B]
no
------
3
5
4
2
1

Is there any good way to achieve this by SQL?

CodePudding user response：

UPDATE "Table B" AS b
SET no = a.no
FROM "Table A"
WHERE b.name = a.name
AND b.no IS NULL

CodePudding user response：

Why not JOIN?

SELECT b.no
FROM [Table A] as a
INNER JOIN [Table B] as b
  ON a.Name=b.Name