EN VI

MySQL Workbench - Update Multiple Rows Base on Multiple Criterias?

How to MySQL Workbench - Update Multiple Rows Base on Multiple Criterias

I have the below table. I want to update the sales price for the product Ball in both row 1 and row 3 based on the Invoice_Date range and the product name.

I have the following sql syntax

update mydb_stock.sql_product_sales set sales_price = 7.5
where invoice_date between '20-03-2024' and '12-04-2024' and product_name = 'Ball';

The above syntax does not work properly. It only update the first row but subsequent rows are not updated.

Invoice_Date	Product_Name	Sales_Price	Quantity	Total_Sales
20-03-2024	Ball	3.5	4	14
25-03-2024	Racket	2.0	3	6.0
12-04-2024	Ball	3.5	3	10.5

I would be grateful if you guys can be of help. Please note that the invoice_date is in varchar. I intentionally did that.

Thanks

Solution:

Please note that the invoice_date is in varchar. I intentionally did that.

Please don't store your dates as text. And if you must do that, you should at least store in YYYY-MM-DD iso format so that they sort properly.

That being said, we can workaround this problem by converting the invoice date to a bona fide date using the STR_TO_DATE() function:

UPDATE mydb_stock.sql_product_sales
SET sales_price = 7.5
WHERE STR_TO_DATE(invoice_date, '%d-%m-%Y') BETWEEN '2024-03-20' AND '2024-12-04' AND
      product_name = 'Ball';

Tags