SQL server updating records where they exist

update [db2].dbo.tblASDF
set deviceID = (select db1.deviceID FROM [database1].[dbo].[device] as db1,[database2].[dbo].[device] as db2 where db1.name = db2.name)
where exists (select * FROM [database1].[dbo].[device] as db1,[database2].[dbo].[device] as db2 where db1.name = db2.name)

Question/Statement1: It's not important that I'm selecting * in the "where exists", what's important is that my "where db1.name = db2.name" clause is returning the rows which meet this condition, because these rows hold the unique keys to help my update to operate? IE, whether I've selected one or 20 columns is irrelevant.

Question2: I'm pulling from 2 databases, db1 and db2. Does the "exists" know to use the unique keys from db1/db2 to update db2's tblASDF.deviceID column? ie, it doesn't matter that there are 2 set开发者_如何学JAVAs of unique keys (the ones for the db1, and the ones for the db2) returned from the "where db1.name = db2.name"?

Q1 Correct. I always do this:

if exists (select 1 from attachments WHERE ID<6) ...

To the reader, it's clearly not relevant WHAT is being returned. In this case if there are any records with an ID of less than 6, then something exists.

Q2 The WHERE Exists clause will resolve to either zero records or more than zero records. In the first case, zero records, the UPDATE will do nothing. Because nothing exists. In the second case, you can replace WHERE EXISTS... with nothing. Your query resolves to

update [db2].dbo.tblASDF  
set deviceID =  (select db1.deviceID FROM [database1].[dbo].[device] as db1 
 ,[database2].[dbo].[device] as db2  where db1.name = db2.name)  

The inner query will not return two sets of unique keys. It will return one set of db1.deviceID IDs where the name is the same in each table. The update will only take place where DB2's DeviceID column in its tblASDF table contains an ID that exists in DB1's equivalent table db1.deviceID in db1.tblASDF.

It will ignore the db2.deviceID column so the IDs in that field are irrelevant.

It must be said that duplicating the two identical pieces of T-SQL is unnessary and it makes the code harder to read. Often I come across SQL with a very, very subtle difference between the two pieces of code, (eg a 1 instead of a 2) thereby introducing a bug.

The "join" is being performed by the WHERE db1.name = db2.name clause

exists returns only true or false, it doesn't have anything to do with keys. Imagine it like it returns true whenever more than one row is returned from the subquery and false when 0 rows returned.

You could replace exists (select * FROM... with exists (select 1 FROM... so that you don't select any column (you can't use the data anyways).

I hope this answers your questions. Please correct me if I'm wrong :)