Friday, June 29, 2012

MS SQL UPDATE with a JOIN

This describes how to UPDATE a table when you have to JOIN another table.  This is for MS SQL Server, so the syntax varies with other versions.

In this example, I am updating the [aspnet_Membership] table, but I want to join the [aspnet_Users] table so I can do it user UserName.  This script allows me to set the password of a user to my password so I can log in as that user.  This is done by updating the ASP.NET Membership table.  I would need to store the [Password] and [PasswordSalt] of this account if I wanted to change it back.



UPDATE m 
SET   m.[Password] = 'mrhLMUfIWCuTHDwOtm1s/I9ABMQsS=='
    , m.[PasswordSalt] = 'suyp+Nsd2lmMPpQ=='
FROM [dnnDB].[dbo].[aspnet_Membership] m
JOIN [dnnDB].[dbo].[aspnet_Users] u on m.UserId = u.UserId
WHERE 1=1
  AND u.UserName in 
  (
      'FirstUser'
    , 'SecondUser'
    , 'ThirdUser'
  )