Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Question Unanswered: How to write this query?

    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.
    Last edited by kspstudy; 04-14-04 at 13:30.

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: How to write this query?

    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

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    UPDATE EMPINFO A
    SET GROUPID = ( SELECT B.GROUPID
    FROM EMPPAYROLLINFO B
    WHERE A.EMPNO = B.EMPNO )
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •