Post reply

Sql server update.

  • January 7, 2010 at 2:57 am

    #140541

    If i have the following table:

    employee table (with the following fields):

    employee table

    emp_id , emp_registered_date

    0001 2011-01-01

    0002 2010-04-01

    0003 2009-01-23

    how do i update the first Id (0001) to take the DATE of the second row, and the second id to take the DATE of the third row.....i am stuck.please help

  • January 7, 2010 at 4:23 am

    #1100105

    Hi, Try this

    CREATE TABLE #TEMP

    (

    SLNO INT IDENTITY(1,1),

    EID VARCHAR(5),

    RDATE DATETIME

    )

    INSERT INTO #TEMP

    SELECT '001','2009-01-01'

    UNION ALL

    SELECT '002','2009-01-05'

    UNION ALL

    SELECT '003','2009-01-10'

    UNION ALL

    SELECT '004','2009-01-15'

    SELECT A.*,B.RDATE FROM

    #TEMP A LEFT OUTER JOIN #TEMP B

    ON A.SLNO = B.SLNO-1

  • January 7, 2010 at 4:43 am

    #1100114

    great yet simple sql query/script.....thanks. mind explaining a bit.

  • January 9, 2010 at 9:51 am

    #1101402

    It's a self join, using the left outer join, to get one version of the table, and a second version of the table matching up the IDs, but off by one value.

    Not sure if this will completely work for you, but if all the IDs are off by one, this will work.

    Moved to T-SQL forum.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply