Solved

SQL Update query using Exists condition

Posted on 2007-04-05
5
367 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
  • 2
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Beautiful! Thanks so much for everyone's comments!
0

Featured Post

Free book by J.Peter Bruzzese, Microsoft MVP

Are you using Office 365? Trying to set up email signatures but you’re struggling with transport rules and connectors? Let renowned Microsoft MVP J.Peter Bruzzese show you how in this exclusive e-book on Office 365 email signatures. Better yet, it’s free!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now