Solved

SQL Update query using Exists condition

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query stumper 3 37
SQL Server Question 5 28
SQL 2008 R2 calc date formula 3 26
Oracle 10g - Select rows into colums 4 12
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

16 Experts available now in Live!

Get 1:1 Help Now