JamesTheBiker JamesTheBiker - 1 month ago 6
SQL Question

Search SQL table for value, lookup another value in that record and replace with value

I want to run a script on a SQL table that will search the table for the users supervisor and then replace the supervisor value with the fullname value. How can I do this? I am using MSSQL and have one table containing this data.

Before:

fullname,username,supervisor
Timothy Dalton,tdalton,rmoore
Pierce Brosnan,pbrosnan,rmoore
Sean Connery,sconnery,rmoore
Roger Moore,rmoore,dcraig
Daniel Craig,dcraig,

After script:

fullname,username,supervisor
Timothy Dalton,tdalton,Roger Moore
Pierce Brosnan,pbrosnan,Roger Moore
Sean Connery,sconnery,Roger Moore
Roger Moore,rmoore,Daniel Craig
Daniel Craig,dcraig,


Thanks

Answer

Try something like this

Update t1
    set t1.supervisor = t2.Fullname
from YourTable t1 
    join YourTable t2 on t1.supervisor = t2.username

This code hasn't been tested ... so make sure to backup table before using it