troubleshooting Question

SQL Server / SQL - how to update a record conditionally ina table by querying another table.

Avatar of splanton
splantonFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2005SQL
2 Comments1 Solution338 ViewsLast Modified:
I am looking for an SQL solution for a data update I need to do - I am out of my depth on this one and have been unable to find a solution anywhere that seems to fit my criteria.

I have a table which needs to have a 'bit' field set to 1 dependant on the result of the existance of a value of a field in another table.

Let me go into more detail

The table that needs updating looks like this:

WasteDetail Table
WasteDetailId (PK, int)
ContractorId (int)
Active (bit)

The second (lookup) table looks like this:

Contractor Table
ContractorId (PK, int)
ContractorTypeId (int)
ContractorSubTypeId (int)

I need to set the 'Active' field on the first table to '1' if there is a value in the 'ContractorTypeId' field in table 2 but only if the 'ContractorId' of table2 = 'ContractorId' of table1...

Any help with this would be greatly appreciated as I have reached an impass on this one.
ASKER CERTIFIED SOLUTION
Aneesh
Database Consultant
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros