Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: I can't believe I'm asking...

    ...But I am struggling to no avail to resolve this simple problem.

    I have a temporary table, which is used as a dumping ground for imports. The important columns here are employee_number and ni_number.

    Some of the imports contain ni_number and no employee_number, some contain both. Example:
    Code:
    +-----------------+-----------+
    | employee_number | ni_number |
    +-----------------+-----------+
    |                 | ABC       |
    |                 | ABC       |
    | 123             | ABC       |
    | 456             | DEF       |
    |                 | GHI       |
    | 789             | GHI       |
    |                 | GHI       |
    +-----------------+-----------+
    Basically I need to fill in the employee_number gaps.

    And before you start beratting me for not being able to get this one done today, provide an answer

    *sigh*
    G
    George
    Home | Blog

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    and I can't believe you didn't provide DDL and an insert statement to populate the table with sample data! need to set an example for the others.


  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    USE TempDb
    
    IF EXISTS(SELECT 1 FROM sysobjects WHERE id = object_id('#gvtemp')) BEGIN
      DROP TABLE #gvtemp
    END
    
    CREATE TABLE #gvtemp (
       employee_number int
     , ni_number       char(3)
    )
    
    INSERT INTO #gvtemp(employee_number, ni_number)
    SELECT NULL, 'ABC' UNION ALL
    SELECT NULL, 'ABC' UNION ALL
    SELECT  123, 'ABC' UNION ALL
    SELECT  456, 'DEF' UNION ALL
    SELECT NULL, 'GHI' UNION ALL
    SELECT  789, 'GHI' UNION ALL
    SELECT NULL, 'GHI'
    
    SELECT * FROM #gvtemp
    
    IF EXISTS(SELECT 1 FROM sysobjects WHERE id = object_id('#gvtemp')) BEGIN
      DROP TABLE #gvtemp
    END
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    UPDATE b
    SET    employee_number = a.employee_number
    FROM   #gvtemp a
     INNER
      JOIN #gvtemp b
        ON a.ni_number = b.ni_number
       AND a.employee_number IS NOT NULL
    George
    Home | Blog

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Code:
    drop table #t 
    
    create table #t (empid int null, col1 int not null)
    
    insert #t
    select 1,2 union all
    select null, 2 union all
    select null, 2 union all
    select null, 2 union all
    select null, 2 union all
    select null, 2 union all
    select 2, 44 union all
    select null, 44 union all
    select null, 44 union all
    select null, 44 union all
    select null, 44 union all
    select null, 44 
    
    select * from #t
    
    update t
    set t.empid=tt.empid
    from #t t
    join #t tt on t.col1=tt.col1
    where t.empid is null
    
    select * from #t

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Let the berating begin!
    George
    Home | Blog

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    i guess you got it then. sometimes all it takes to figure out the answer is to ask the question.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also, ni_number is a weird name for a char(3) column

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're right, thanks Jezemine, I think all it took was to ask.

    And you're right, but it works perfectly in an example
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ni_number = (I suspect) National Insurance Number (UK). For once the government is at fault, not George.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "for once"

    LMAO
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots is right, ni_number = national insurance "number".

    Interestingly, if you look at the real table, employee_number is a char(6) field (for historic reasons it has to retain leading zeroes).

    Man I need to sleep!
    George
    Home | Blog

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it should be na_number (for Not A Number)

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This reminds me of when I was last in the bank, and I was setting up a new account with internet banking, and the guy asks me for a password, to which I respond with "Can it be alphanumeric" and he just looks at me and goes "How do you spell that?"


    Thank you, I'm here all week!
    George
    Home | Blog

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that joke would kill at Chortles.

Posting Permissions

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