asked on

SQL Update query using Exists condition

I need some help with an update query. I'm awarding 500 points because it's urgent.

I have 3 tables, company, site, and product. The common field in all 3 tables is the site_id. I'm trying to set a status field to 0 in all 3 tables. The company table is updated first, then the site, and then the product. The problem is there are multiple sites and each have multiple products. We are coding in C#.

Here are my queries so far:
Update company table: "UPDATE hades_company_info SET company_status = 0, company_modified_date = '" + dtNow + "', company_modified_by = '" + pubUserID2 + "' WHERE company_id = '" + pubCompId + "'";

Update site table:
UPDATE hades_site_info SET site_status = 0 WHERE company_id = '" + pubCompId + "'"

I believe that I need an exists condition for the product table. There is no company_id field in the product table. I need to link the site.company_id = company.company_id somehow.

If you need more information, please let me know. Thanks ahead of time.

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MOA81

since you want to set the status to zero for all fields why put a condition in the first place
update company set status = 0
update product set status = 0
update site set status = 0

I am missing the point doing this ?

pagefigaro

ASKER

I need a condition because there are specific products for specific sites and there might be 10 sites under a company that i want to be status = 0. i don't want to to have to grab all of those site_ids and then do a foreach loop for each product. i want to be able to link the site.company_id = company.company_id. Hope that makes better sense.

I'm trying the query by appari now.

MOA81

update product
set status = 0
from company inner join site on company.site=site.site
inner join products on site.site = product.site

this way u insure that the product belongs to the correct site and each site belongs to the correct company

pagefigaro

ASKER

Beautiful! Thanks so much for everyone's comments!