Solved

SQL Update query using Exists condition

Posted on 2007-04-05
5
373 Views
Last Modified: 2010-04-20
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.
0
Comment
Question by:pagefigaro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 18857879
try something like this

"update product set status = 0
from product
where procustid in ( select productid from hades_site_info WHERE company_id = '" + pubCompId + "') "

0
 
LVL 8

Expert Comment

by:MOA81
ID: 18857933
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 ?
0
 

Author Comment

by:pagefigaro
ID: 18858012
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.
0
 
LVL 8

Expert Comment

by:MOA81
ID: 18858078
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  
0
 

Author Comment

by:pagefigaro
ID: 18858213
Beautiful! Thanks so much for everyone's comments!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question