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
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';