473,320 Members | 2,024 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Question about Update Statement - WHERE EXISTS

I found this example, and I'm curious as to what the WHERE EXISTS part of it does - it seems to simply repeat what was stated before. How would functionality differ if it were left off?

UPDATE suppliers
SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);
Mar 12 '08 #1
5 4262
ck9663
2,878 Expert 2GB
I found this example, and I'm curious as to what the WHERE EXISTS part of it does - it seems to simply repeat what was stated before. How would functionality differ if it were left off?

UPDATE suppliers
SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);

Yes. It can be re-written simply as:


Expand|Select|Wrap|Line Numbers
  1. UPDATE suppliers
  2. set supplier_name = customer.name
  3. from customer 
  4. where customer.customer_id = suppliers.supplier_id
-- CK
Mar 12 '08 #2
balame2004
142 100+
Hi,

You can also use the following query for updating records. It uses Join operator.

UPDATE Suppliers Set Supplier_Name=c.Customer_Name FROM
Suppliers s JOIN Customers c where s.Supplier_Id=c.Customer_Id


Balaji
Mar 13 '08 #3
balame2004
142 100+
Hi,

The above post is wrong.

You can also use the following query for updating records. It uses Join operator.

UPDATE Suppliers Set Supplier_Name=c.Customer_Name FROM
Suppliers s JOIN Customers c ON s.Supplier_Id=c.Customer_Id


Balaji
Mar 13 '08 #4
I may be overlooking something, but I'm not sure the original query and the suggested queries do the same thing. It looks like the original query wants to update the [supplier_name] column in [suppliers] if and only if there exists a customer in the [customers] table with a matching ID. The suggested queries would overwrite [supplier_name] to NULL if the condition did not exist, and the intention in the SQL statement that HughManity found elsewhere might have been to leave [supplier_name] alone in that case.

In other words, if we have customers and suppliers, and we manually assign them an ID and we make those ID's the same if the client is both a customer and a supplier of our company, then we want to "import" the customer name to the suppliers table, perhaps to fix up manual changes that other users have made, but leave everyone else's name alone.

Am I seeing that correctly? Or can someone give me a cyber knock alongside the head... ;-)
Mar 13 '08 #5
balame2004
142 100+
I may be overlooking something, but I'm not sure the original query and the suggested queries do the same thing. It looks like the original query wants to update the [supplier_name] column in [suppliers] if and only if there exists a customer in the [customers] table with a matching ID. The suggested queries would overwrite [supplier_name] to NULL if the condition did not exist, and the intention in the SQL statement that HughManity found elsewhere might have been to leave [supplier_name] alone in that case.

In other words, if we have customers and suppliers, and we manually assign them an ID and we make those ID's the same if the client is both a customer and a supplier of our company, then we want to "import" the customer name to the suppliers table, perhaps to fix up manual changes that other users have made, but leave everyone else's name alone.

Am I seeing that correctly? Or can someone give me a cyber knock alongside the head... ;-)

Hi,

No, you understand something wrong. It won't change the records that are not matching.

Regards,
Balaji U
Apr 11 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Mohammed Mazid | last post by:
Can anyone please help me here? Basically I have modified the source code and understood it but when I update a record in the db using a JSP, it gives me an error "The flight you selected does...
6
by: Karen Middleton | last post by:
In MS Access I can do in one SQL statement a update if exists else a insert. Assuming my source staging table is called - SOURCE and my target table is called - DEST and both of them have the...
9
by: Javier | last post by:
Hi I've a table with 2 columns, one for a client code and one for a date/time and could be more than one record with the same client code and date/time. the 3rd column is another date/time, NULL...
2
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an...
1
by: vj | last post by:
How to Update multiple tables in a single SQL update Statement? Is there any way out? vj.
3
by: Ken H | last post by:
Hi I have a question about architecting solutions.. I have a part of a project which requires me to track person details (name, addresses, etc... Should I be creating Person objects, Address...
9
by: rhaazy | last post by:
Using MS SQL 2000 I have a stored procedure that processes an XML file generated from an Audit program. The XML looks somewhat like this: <ComputerScan> <scanheader>...
5
by: Sphenix | last post by:
------------------------ UPDATE A SET A.ID = '?' + A.ID FROM TABLEA A LEFT OUTER JOIN TABLEB B ON A.INDEX = B.INDEX WHERE B.DUP_ID IS NULL ------------------------ seems like update with...
3
by: annecarterfredi | last post by:
I have two tables...Table1(name, date); Table2(appointment_name, name, appointment_date). I want to update appointment_date in Table2 with date from Table1 (business requirement). I am trying to...
10
by: =?ISO-8859-1?B?UOlw6g==?= | last post by:
Hi all. Im a newbie in PHP and im trying to upload a file to the server. I use a form to upload a pdf file and some text information about it. The client uploads the file and the system...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.