Hello, I have a table that contains two types of users: customers and partners
Customers choose partners from a list when creating an account.
I had to recreate all the accounts which gave new accountId to all accounts. I now need to update the customer's partnerId with the new accountId of their chosen partner. Make sense?
These are the fields that matter: accountId, partnerid, oldaccountId
I thougt that if I put all the new accountid and oldaccoutid in a table I could use that as a join table, but that is not updating the old partnerId with the new accountid. below is my update statement that is not working.
create table #Temp(
select accountId, oldaccountid from accounts
set partnerId = b.nid
from #Temp b, accounts a
where a.partnerid = b.oid