Similar Query Behaving Differently

user_11677593 0
Viewed 33

I Have 2 similar looking Queries giving 2 different result ,Not able to see the difference in query structure though

Query 1

UPDATE csv_Customer_Master,Customer_Master
SET
Customer_Master.Point_Balance=csv_Customer_Master.Point_Balance
WHERE csv_Customer_Master.Customer_Id = Customer_Master.Customer_Id

Query 2

UPDATE Customer_Master
SET Point_Balance = (
SELECT Point_Balance
FROM csv_Customer_Master
WHERE csv_Customer_Master.Customer_Id = Customer_Master.Customer_Id)

query 1 is updating Customer_Master for just the customers in csv_Customer_Master for Point_Balance nothing else

query2 is updating Customer_Master for the customers in csv_Customer_master and then updating rest of the Point_Balance column to 0 for all other customers in Customer_Master

To me both the queries are the same but not sure why all other customers are getting updated to 0 Column Type for Point_Balance is Decimal(15,2) Default -1

mysql sql
1 Answers

In the first query the WHERE clause is filtering on the table being updated - so limits which records in that table are updated.

In the second query the WHERE clause is a filter in the calculation on the right of the '=' sign, so affects the value that Point_Balance is set to, not which records in Customer_Master are being updated

user_136583990
Related