[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

need a query

I need an update query.  I have two Employee tables, let's say Emp1 and Emp2.   They both have a primary key of user_id.  userid = 1 in one table is the same employee as userid = 1 in the other table.

 I want to compare Emp1 to Emp2.  I know I can do it one by one.  However, I was wondering if I can do it in one query.  For any employee that exists in Emp2 but not in Emp1, I want to set a bit column in Emp2 called "deleted" to 1.


Thanks.
0
HLRosenberger
Asked:
HLRosenberger
5 Solutions
 
UnifiedISCommented:
UPDATE Emp2
SET deleted = 1
WHERE Emp2.user_id NOT IN (SELECT user_id FROM Emp1)
0
 
Evan CutlerCommented:
SELECT userid
FROM table1
WHERE NOT EXISTS
    (SELECT userid from table2);

Cheers,
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Update Emp2
SET deleted = 1
FROM emp2
WHERE NOT EXISTS (SELECT 1 FROM emp1 where emp1.employeeid = emp2.employeeID )
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
-- This should return the 'exists in Emp2 but not in Emp1'
SELECT *
FROM Emp2
   LEFT JOIN Emp1 ON Emp2.user_id = Emp1.user_id
WHERE Emp1.user_id IS NULL

-- Here's the update
UPDATE Emp2
SET Emp2.deleted = 1
FROM Emp2
   LEFT JOIN Emp1 ON Emp2.user_id = Emp1.user_id
WHERE Emp1.user_id IS NULL
0
 
HLRosenbergerAuthor Commented:
Thanks to all.  Can this be done if the tables on in two different databases on the same server?
0
 
Evan CutlerCommented:
yes, if the user has at least read access on the select side, and write access on the update side.

Database.Owner.Table
0
 
HLRosenbergerAuthor Commented:
Thanks to all!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now