Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Update Query with JOIN and TOP(1)

    I am using SQL Sever 2005. I have a question dealing with 2 separate tables. The first table is EMP_W2 and has the following structure (some columns were left out since they don't have anything to do with the question/issue):

    YEAR [decimal](4, 0),
    EMPID [nchar](9),
    SSN [decimal](9, 0),
    W2CODE [nchar](3),
    BX12_AMT1 [decimal](7, 2),
    BX12_AMT2 [decimal](7, 2),
    BX12_AMT3 [decimal](7, 2),
    BX12_AMT4 [decimal](7, 2),
    BX12_CODE1 [nchar](2),
    BX12_CODE2 [nchar](2),
    BX12_CODE3 [nchar](2),
    BX12_CODE4 [nchar](2),

    Sample data for EMP_W2 is as follows:

    2007, LEHLI01, 555555555, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
    2008, LEHLI01, 555555555, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL

    The second table is EMP_W2_ORG which has the following layout:

    [E2YEAR] [decimal](4, 0),
    [E2EID] [nchar](9),
    [E2SSN] [decimal](9, 0),
    [E2BOX] [nchar](3),
    [E2CODE] [nchar](3),
    [E2AMT] [decimal](9, 2)

    Sample data for EMP_W2_ORG is as follows:

    2008 LEHLI01 555555555 12 G 450.00
    2008 LEHLI01 555555555 12 H 250.00
    2008 LEHLI01 555555555 12 I 150.00
    2008 LEHLI01 555555555 12 F 85.00


    What I am trying to do is to put each line/code from EMP_W2_ORG into the EMP_W2 table.

    Example would be starting with this:
    2008, LEHLI01, 555555555, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
    and ending with this:
    2008, LEHLI01, 555555555, 12, 450.00, 250.00, 150.00, 85.00, G, H, I, F


    I have multiple employees and years. The codes aren't only G, H, I, F that is just for this employee, there could be other codes, it just depends. I have been able to update other columns but because I need to select the 1st, 2nd, 3rd, and 4th row per empid and year I can't seem to find a way to get it to work. This is what I have so far (but I know its not right):

    UPDATE EMP_W2 SET BX12_AMT1 = E2AMT, BX12_CODE1 = E2CODE FROM EMP_W2 INNER JOIN EMP_W2_ORG ON YEAR = E2YEAR AND EMPID = E2EID WHERE E2BOX = '12'

    I tried to do another update using TOP(1) but it doesn't get the TOP(1) per EMPID and YEAR, just the TOP(1) of the whole select. This is for a database conversion but I have the original table in SQL Server 2005 already so that isn't a issue.

    Any ideas?????

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work:
    Code:
    CREATE table EMP_W2 (
    	[YEAR]		[decimal](4, 0),
    	EMPID		[nchar](9),
    	SSN			[decimal](9, 0),
    	W2CODE		[nchar](3),
    	BX12_AMT1	[decimal](7, 2),
    	BX12_AMT2	[decimal](7, 2),
    	BX12_AMT3	[decimal](7, 2),
    	BX12_AMT4	[decimal](7, 2),
    	BX12_CODE1	[nchar](2),
    	BX12_CODE2	[nchar](2),
    	BX12_CODE3	[nchar](2),
    	BX12_CODE4	[nchar](2)
    )
    INSERT INTO EMP_W2([YEAR], EMPID, SSN) VALUES(2008, 'LEHLI01', 555555555)
    
    CREATE table EMP_W2_ORG (
    	[E2YEAR]	[decimal](4, 0),
    	[E2EID]		[nchar](9),
    	[E2SSN]		[decimal](9, 0),
    	[E2BOX]		[nchar](3),
    	[E2CODE]	[nchar](3),
    	[E2AMT]		[decimal](9, 2)
    )
    
    INSERT INTO EMP_W2_ORG([E2YEAR], [E2EID], [E2SSN], [E2BOX], [E2CODE], [E2AMT])
    VALUES(2008, 'LEHLI01', 555555555, '12', 'G', 450.00)
    INSERT INTO EMP_W2_ORG([E2YEAR], [E2EID], [E2SSN], [E2BOX], [E2CODE], [E2AMT])
    VALUES(2008, 'LEHLI01', 555555555, '12', 'H', 250.00)
    INSERT INTO EMP_W2_ORG([E2YEAR], [E2EID], [E2SSN], [E2BOX], [E2CODE], [E2AMT])
    VALUES(2008, 'LEHLI01', 555555555, '12', 'I', 150.00)
    INSERT INTO EMP_W2_ORG([E2YEAR], [E2EID], [E2SSN], [E2BOX], [E2CODE], [E2AMT])
    VALUES(2008, 'LEHLI01', 555555555, '12', 'F', 85.00);
    
    select * from EMP_W2;
    
    WITH cte as
    (SELECT [E2YEAR], 
    	[E2EID], 
    	[E2SSN], 
    	[E2BOX], 
    	[E2CODE], 
    	[E2AMT], 
    	ROW_NUMBER() OVER (PARTITION BY [E2YEAR], [E2EID], [E2SSN] ORDER BY [E2CODE]) as RowNum
    FROM EMP_W2_ORG
    )
    UPDATE EMP_W2 SET
    	W2CODE = [E2BOX],
    	BX12_AMT1 = [E2AMT],
    	BX12_CODE1 = [E2CODE]
    FROM cte 
    WHERE [YEAR] = [E2YEAR] AND
    	EMPID = [E2EID] AND
    	SSN = [E2SSN] AND
    	RowNum = 1;
    
    
    WITH cte as
    (SELECT [E2YEAR], 
    	[E2EID], 
    	[E2SSN], 
    	[E2BOX], 
    	[E2CODE], 
    	[E2AMT], 
    	ROW_NUMBER() OVER (PARTITION BY [E2YEAR], [E2EID], [E2SSN] ORDER BY [E2CODE]) as RowNum
    FROM EMP_W2_ORG
    )
    UPDATE EMP_W2 SET
    	BX12_AMT2 = [E2AMT],
    	BX12_CODE2 = [E2CODE]
    FROM cte 
    WHERE [YEAR] = [E2YEAR] AND
    	EMPID = [E2EID] AND
    	SSN = [E2SSN] AND
    	RowNum = 2;
    
    
    WITH cte as
    (SELECT [E2YEAR], 
    	[E2EID], 
    	[E2SSN], 
    	[E2BOX], 
    	[E2CODE], 
    	[E2AMT], 
    	ROW_NUMBER() OVER (PARTITION BY [E2YEAR], [E2EID], [E2SSN] ORDER BY [E2CODE]) as RowNum
    FROM EMP_W2_ORG
    )
    UPDATE EMP_W2 SET
    	BX12_AMT3 = [E2AMT],
    	BX12_CODE3 = [E2CODE]
    FROM cte 
    WHERE [YEAR] = [E2YEAR] AND
    	EMPID = [E2EID] AND
    	SSN = [E2SSN] AND
    	RowNum = 3;
    
    
    WITH cte as
    (SELECT [E2YEAR], 
    	[E2EID], 
    	[E2SSN], 
    	[E2BOX], 
    	[E2CODE], 
    	[E2AMT], 
    	ROW_NUMBER() OVER (PARTITION BY [E2YEAR], [E2EID], [E2SSN] ORDER BY [E2CODE]) as RowNum
    FROM EMP_W2_ORG
    )
    UPDATE EMP_W2 SET
    	BX12_AMT4 = [E2AMT],
    	BX12_CODE4 = [E2CODE]
    FROM cte 
    WHERE [YEAR] = [E2YEAR] AND
    	EMPID = [E2EID] AND
    	SSN = [E2SSN] AND
    	RowNum = 4;
    
    select * from EMP_W2
    
    --DROP table EMP_W2
    --DROP table EMP_W2_ORG
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    Thank you so much!!! I tweaked it to limit to only E2BOX = '12' and it was perfect. Thank you again!


    WITH cte as
    (SELECT [E2YEAR],
    [E2EID],
    [E2SSN],
    [E2BOX],
    [E2CODE],
    [E2AMT],
    ROW_NUMBER() OVER (PARTITION BY [E2YEAR], [E2EID], [E2SSN] ORDER BY [E2CODE]) as RowNum
    FROM EMP_W2_ORG WHERE E2BOX = '12'
    )
    UPDATE EMP_W2 SET
    BX12_AMT2 = [E2AMT],
    BX12_CODE2 = [E2CODE]
    FROM cte
    WHERE [YEAR] = [E2YEAR] AND
    EMPID = [E2EID] AND
    SSN = [E2SSN] AND
    RowNum = 2;

Posting Permissions

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