Question about Update Statement - WHERE EXISTS

[HughManity]

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.custo mer_id = suppliers.suppl ier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.custo mer_id = suppliers.suppl ier_id);

[ck9663]

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.custo mer_id = suppliers.suppl ier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.custo mer_id = suppliers.suppl ier_id);

Yes. It can be re-written simply as:

UPDATE suppliers
set supplier_name = customer.name
from customer 
where customer.customer_id = suppliers.supplier_id

-- CK

[balame2004]

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

[balame2004]

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

[FredSovenix]

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... ;-)

[balame2004]

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