Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – how to update one column by adding interval to another column

I have been trying to update one column in a table by adding a 5 YEAR interval to another column, and always get syntax error exception. The following is my PHP script:

AppModelsMyModel::where('cl1', $cl1Value)
              ->where('cl2', ">=", $startingFrom)
              ->where('cl2', '<=', $endingAt)
              ->update([
                         'cl3'  =>  DB::raw('date_add(cl2 + INTERVAL 5 YEARS)')
                      ]);

cl1 is of data type int, cl2 and cl3 are of data type timestamp. #cl1Value, $startingFrom and $endingAt are passed from other parts of the script. Ad pointed out in the answer below, there is an syntax error related to date_add(), but even with that fixed, I still similar error message. There seems to be something wrong with how I passed the number 5.

The following is error message when I ran this script.

IlluminateDatabaseQueryException : SQLSTATE[42601]: Syntax error: 7
ERROR: syntax error at or near "5" LINE 1: … "cl3" = date_add(cl2 +
INTERVAL 5 YEARS) w…
^ (SQL: update "my_models" set "cl3" = date_add(cl2 + INTERVAL 5 YEARS) where
"cl1" = 630 and "cl2" >= 2024-09-01 00:00:01 and "cl2" <= 2024-12-15
00:00:00)

Could someone advise what I did wrong here? If I run the generated SQL script or other variant such as the following in mysql console, I don’t get syntax error.

->update(['cl3'  =>  DB::raw('cl2 + INTERVAL 5 YEARS')])

UPDATE my_models SET cl3 = cl2 + INTERVAL 5 YEAR WHERE cl2 >= '2024-09-01 00:00:00' AND cl2 <= '2024-12-15 00:00:00' AND cl1 = 630;
Query OK, 0 rows affected (0.003 sec)
Rows matched: 0  Changed: 0  Warnings: 0

The only difference I can see is the timestamp string is escaped in the script I entered into mysql console.

I am running this from Laravel v6 + MariaDB v10.11.10.

Tags:

2

Answers


  1. 0

    The syntax of the function date_add is incorrect. Replace the plus sign for a comma in the function, like this:

    AppModelsMyModel::where('cl1', $cl1Value)
              ->where('cl2', ">=", $startingFrom)
              ->where('cl2', '<=', $endingAt)
              ->update([
                         'cl3'  =>  DB::raw('date_add(cl2, INTERVAL 5 YEARS)')
                      ]);
    Login or Signup to reply.
  2. 0

    As you are using MariaDB, so you should use TIMESTAMPADD which is a MariaDB-Specific Alternative. MariaDB provides a function TIMESTAMPADD to perform such operation:

    Amend your codes like this –

    AppModelsMyModel::where('cl1', $cl1Value)
    ->where('cl2', '>=', $startingFrom)
    ->where('cl2', '<=', $endingAt)
    ->update(['cl3' => DB::raw('TIMESTAMPADD(YEAR, 5, cl2)')]);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search