CODEWITHSUNDEEP
sqlsql-server
TechPro
TechPro

Reputation: 331

SQL Insert doing conditional checking

I have written this SQL query that returns the users with first and last name if same name is found in two different databases database1 and database2, if the user's status is 'employee' in one of the tables in database1.

SELECT distinct 
    FirstName, LastName
FROM  
    database1.dbo.test1 a 
JOIN
    database1.dbo.test2 b ON b.id = a.id
JOIN
    database1.dbo.test3 c ON a.id = c.id
JOIN 
    database2.dbo.test d ON a.firstname + ' ' + a.lastname = d.firstname + ' '+ d.lastname
WHERE
    c.status = 'employee'

Now, I need to compare this first and last names I got using above query with the first and last name in database "database2" and if match is found I need to insert in column "isemployee" as yes. Can you please suggest how can I apply the condition to compare the names I got using above Select query and the names in database2 and insert in column "isemployee" of database2 if name match is found or condition is true.

Upvotes: 0

Views: 46

Answers (2)

warwreken
warwreken

Reputation: 104

You can use the code below...

UPDATE d SET isemployee = 'YES!'
FROM  
database1.dbo.test1 a 
join database1.dbo.test2 b on b.id = a.id
join database1.dbo.test3 c on a.id = c.id
join database2.dbo.test d
on a.firstname + ' ' + a.lastname = d.firstname + ' '+ d.lastname
where 
c.status = 'employee'

All you need to do is to use your own query to update the column that you want, because the comparison already had been made

Upvotes: 2

JamieD77
JamieD77

Reputation: 13949

You could something like this using Exists

UPDATE  d2
SET     d2.isemployee = 1
FROM    database2.dbo.test d2
WHERE   EXISTS ( SELECT *
                 FROM   database1.dbo.test1 a
                        JOIN database1.dbo.test2 b ON b.id = a.id
                        JOIN database1.dbo.test3 c ON a.id = c.id
                 WHERE  c.status = 'employee'
                        AND a.firstname = d2.firstname
                        AND a.lastname = d2.lastname )

Upvotes: 2

Related Questions