There are 2 tables with this layout for example.


1. Table Name - EMPINFO with below columns

EMPNO INTEGER
FNAME CHAR(20)
LNAME CHAR(20)
BAND CHAR(1)
SALARY DECIMAL(12,2)
BENEFIT_FLG_1 CHAR(1)
BENEFIT_FLG_2 CHAR(1)

PRIMARY KEY - EMPNO


2. Table Name - EMPPAYROLLINFO with below columns
EMPNO INTEGER
GROUPID CHAR(4)
SALARY DECIMAL(12,2)
TAX_FEDERAL DECIMAL(10,2)
TAX_STATE DECIMAL(10,2)
MEDICARE DECIMAL(10,2)
BENEFIT_FLG_1 CHAR(1)
BENEFIT_FLG_2 CHAR(1)

PRIMARY KEY EMPNO


Now if i add the GROUPID column to EMPINFO table, how do i transfer the data in GROUPID column of EMPPAYROLLINFO to the EMPINFO table using SQL?

There are about 5k rows in each.

Yor responses are appreciated.

Have you tried this:

UPDATE EMPINFO EI
SET GROUPID = ( SELECT MAX(GROUPID)
FROM EMPPAYROLLINFO EPRI
WHERE EPRI.EMPNO = EI.EMPNO )

...in the manual it's called a scalar-subselect.

Cheers, Bill

Thanks for your response hurmavi/Bill, but it will not serve my purpose because i do not want the Max or min or avg or any such functions. Each EMPNO will have a different GROUPID and i just want to copy their values and not the max. value.

UPDATE EMPINFO A
SET GROUPID = ( SELECT B.GROUPID
FROM EMPPAYROLLINFO B
WHERE A.EMPNO = B.EMPNO )