CODEWITHSUNDEEP
sqlsql-servert-sql
Tim
Tim

Reputation: 2911

Copy one cell from one database to another

I am having difficulty creating a SQL statement. There is a password in a test database that I have forgotten, and I need to replace it with a known password from a different test database. The password is stored as binary, so I can't seem to right click on it to copy and paste, so I am trying to write a SQL statement. Unfortunately, although I can figure out how to insert a row from one database to another, and copy a cell from one table to another, I can't seem to get the cell from one database to another.

Thus, what I am trying to do is something like this:

use TestDb1
GO

UPDATE TestDb1.[Staff].[User] (Password)
{
    SELECT TestDb2.[Staff].[User].Password
    FROM TestDb2.[Staff].[User]
    WHERE UserName = 'admin'
} 
WHERE UserName = 'admin'

I know, this isn't even close to being correct, but I suppose if I was close I wouldn't really need help.

Upvotes: 1

Views: 342

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300529

The ANSI TSQL syntax for UPDATES with joins can be confusing (so confusing that I made an error the first time around :) ):

UPDATE u1 
Set u1.Password = u2.Password 
from TestDb1.[Staff].[User] u1
join TestDb2.[Staff].[User] u2 on u2.UserName = u1.UserName 
WHERE u2.UserName = 'admin'

[SQLFiddle that shows the syntax against the same table.]

Upvotes: 2

Related Questions