By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,688 Members | 1,715 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,688 IT Pros & Developers. It's quick & easy.

Question about Update Statement - WHERE EXISTS

P: 6
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
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
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

100+
P: 142
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

100+
P: 142
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

P: 10
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

100+
P: 142
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

Post your reply

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