Mysql Question posted in
The official MySQL documentation can be found here.

Run UPDATE mySQL Query on Multiple Rows

I have a table like this

librarylog

patronName bookId dueDate
John 24 2024-01-14
Sam 42 2024-01-07
Bob 13 2024-01-07

I’m trying to create a SQL query which updates all the dueDate values and increases them by a week.

This is what I had:

UPDATE librarylog SET dueDate = DATE_ADD((SELECT * FROM (SELECT dueDate FROM librarylog)tblTmp), INTERVAL 1 WEEK);

But this returns

#1242 - Subquery returns more than 1 row

Tags:

2

Answers


  1. 0

    The RHS of the update should just be the dueDate column wrapped in DATE_ADD:

    UPDATE librarylog
SET dueDate = DATE_ADD(dueDate, INTERVAL 1 WEEK);  -- update all records
    Login or Signup to reply.
  2. 0

    It looks like you are using MySQL syntax for your SQL query. However, the subquery you are using is returning multiple rows, which is causing an error. You should use a single value for the DATE_ADD function.

    Here’s a corrected version of your query:

    UPDATE librarylog SET dueDate = DATE_ADD(dueDate, INTERVAL 1 WEEK);

    This query directly updates the dueDate column by adding one week to the existing values. No need for a subquery in this case.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search