Search code examples
mysqlsqldatediffmysql-error-1093mysql-error-1242

Update For all loans WHERE user_id=? in CASE when DATEDIFF() >0. Error 1242 and 1093

I have this query 

Update loans set fine = case 
when DATEDIFF((
(SELECT * from (SELECT loans.due_date FROM loans where users_id = 1)  AS l1)) 
,DATE(NOW())) > 0 THEN 1
ELSE fine 
END 
WHERE users_id =1;

From table

+-------------+
| Field       |
+-------------+
| users_id    |
| books_isbn  |
| aquire_date |
| due_date    |
| fine        |
+-------------+

It's work with user that have just one 'loans' but I don't know how to make it work with several 'loans' of one user. =(

Solution

  • I would expect a query to look someting like this:

    Update loans l
    set fine = 1
    where users_id = 1 and l.due_date < curdate();

    It is a little tricky to figure out the exact logic without sample data and desired results.