How to update a table with null values with data from other table at one time?

I have 2 tables - A and B . Table A has two columns, pkey (primary key) and col1. Table B also has two columns, pr_key (primary key but not a foreign key) and column1. 开发者_JAVA技巧 Both tables have 4 rows. Table B has no values in column1, while table A has column1 values for all 4 rows. So my data looks like this

Table A 
pkey col1 
A    10 
B    20  
C    30 
D    40 

Table B 
pr_key column1 
A      null 
B      null 
C      null 
D      null

I want to update table B to set the column1 value of each row equal to the column1 value of the equivalent row from table A in a single DML statement.

---

Should be something like that (depends on SQL implementation you use, but in general, the following is rather standard. In particular should work in MS-SQL and in MySQL.

INSERT INTO tblB (pr_key, column1)
  SELECT pkey, col1
  FROM tblA
  -- WHERE some condition (if you don't want 100% of A to be copied)

The question is a bit unclear as to the nature of tblB's pr_key, if for some reason this was a default/auto-incremented key for that table, it could just then be omitted from both the column list (in parenthesis) and in the SELECT that follows. In this fashion upon insertion of each new row, a new value would be generated.

Edit: It appears the OP actually wants to update table B with values from A. The syntax for this should then be something like

UPDATE tblB 
SET Column1 = A.Col1
FROM tblA AS A
JOIN tblB AS B ON B.pr_key = A.pkey

---

This may perform better:

MERGE INTO tableB
USING (select pkey, col1 from tableA) a
ON (tableB.pr_key = a.pkey)
WHEN MATCHED THEN UPDATE
SET tableB.column1 = a.col1;

---

It sounds like you want to do a correlated update. The syntax for that in Oracle is

UPDATE tableB b
   SET column1 = (SELECT a.column1
                    FROM tableA a
                   WHERE a.pkey = b.pr_key)
 WHERE EXISTS( SELECT 1
                 FROM tableA a
                WHERE a.pkey = b.pr_key )

The WHERE EXISTS clause isn't necessary if tableA and tableB each have 4 rows and have the same set of keys in each. It is much safer to include that option, though, to avoid updating column1 values of tableB to NULL if there is no matching row in tableA.