MySQL 8.0: Difficulty updating rows with a WHERE condition on a calculated field
I'm performance testing and I'm following best practices but I'm working on a project and hit a roadblock... I've encountered a strange issue with Hey everyone, I'm running into an issue that's driving me crazy. I'm working with an scenario with MySQL 8.0 when trying to update rows based on a calculated field. I have a table called `orders` with a column `order_date` and I want to update the `status` field for orders that are older than 30 days. The query I wrote is as follows: ```sql UPDATE orders SET status = 'archived' WHERE DATEDIFF(CURDATE(), order_date) > 30; ``` However, when I run this query, it doesn't seem to update any rows, even though there are definitely orders older than 30 days. I checked the `order_date` format, and it is `YYYY-MM-DD`, which should be fine. I also tried this query: ```sql SELECT * FROM orders WHERE DATEDIFF(CURDATE(), order_date) > 30; ``` This select query returns the expected rows, so I know the condition is correct. I've also tried the following variations: ```sql UPDATE orders SET status = 'archived' WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY); ``` This also does not yield any updates. The strange part is that there's no behavior message returned, and the `status` column remains unchanged. I’ve confirmed that my user has the necessary permissions to perform updates on the `orders` table. I’ve also checked for triggers that might be interfering, but none exist. Could this be a version-specific scenario or am I missing something in my query logic? Any insights would be greatly appreciated! For context: I'm using Sql on Linux. What's the best practice here? Could someone point me to the right documentation? I recently upgraded to Sql 3.10. I'd be grateful for any help. I'm working in a Windows 11 environment. I'm working with Sql in a Docker container on Ubuntu 22.04.