#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    4
    Rep Power
    0

    Stuck on something that seems quite simple


    I have a table with 10's of thousands of records each stamped with the user id from active directory, some bright spark has changed the user id's of 10 of the users in AD. So now there is no reference to the past work of those users. I have a xref table with the old and new id's for these users. How do I update the old user id in the main table with the new id only for users existing in the xref table?

    I just can not get it to work.

    Any suggestions welcome (even if I have to add a new column short term).

    Rob
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    could you write a join query that matches the user rows to be replaced?

    that way i can rewrite it as an UPDATE without having to guess at your table and column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    could you write a join query that matches the user rows to be replaced?

    that way i can rewrite it as an UPDATE without having to guess at your table and column names


    Sorry about that it was late and yesterday was a long day.

    The xref table is calles LOGON and has two columns NEW_ID and OLD_ID. In the target table (User_Info) the column to be changed is User_ID

    The two queries I tried are below, I think both should work but they just wouldn't run. I'll give it another go when I get back into work.

    UPDATE User_Info
    SET User_ID = (SELECT NEW_ID FROM LOGON WHERE OLD_ID = User_Info.User_ID)
    WHERE User_ID IN (SELECT DISTINCT(OLD_ID) FROM LOGON)

    and

    UPDATE User_Info
    SET User_ID = (SELECT NEW_ID FROM LOGON WHERE OLD_ID = User_Info.User_ID)
    WHERE EXISTS (SELECT OLD_ID FROM LOGON WHERE OLD_ID = User_Info.User_ID)

    Rob
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    well, that wasn't quite the join query that i had requested, but it'll do...
    Code:
    UPDATE user_info
       SET user_id = t2.new_id
      FROM user_info AS t1
    INNER
      JOIN logon AS t2
        ON t1.user_id = t2.old_i
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    4
    Rep Power
    0
    Thanks


    I'll give it a go.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    4
    Rep Power
    0
    Thanks but no luck, the query part is ok, but the update fails as it can't "bond" the field to be updated.

    Thanks for trying.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by rob.b
    ... the update fails as it can't "bond" the field to be updated.
    never seen that error message before

    are you sure that's what it says?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo