Solved

SQL Update query using Exists condition

Posted on 2007-04-05
5
372 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 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