MySQL - How to set to NOW() and NULL while inserting a row into a table?

Question

INSERT INTO rental (rental_date, last_update, return_date)
    SET rental_date, last_update = NOW()
    AND return_date = NULL.

I'm trying to insert rental_date, last_update and return_date into the table rental while also setting their timestamp to NOW() and NULL.

Answer 1

The syntax should be more like this.

INSERT INTO rental (rental_date, last_update, return_date)
SET
    rental_date = NOW()
  , last_update = NOW()
  , return_date = NULL   

While MySQL support this INSERT INTO ... SET syntax it is better to use ANSI SQL Standard INSERT INTO ... VALUES like so

INSERT INTO rental
  (rental_date, last_update, return_date) 
VALUES
  (NOW(), NOW(), NULL)

Answer 2

You are mixing two different styles of INSERT.

Either use the syntax

INSERT INTO table (column1, column2) VALUES (value1, value2)

or use

INSERT INTO table SET column1 = value1, column2 = value2

but don't mix both.

Your values NOW() and NULL are basically correct so it's simply the way you do the insert.

Answer 3

You can refer to the answer from this link https://stackoverflow.com/a/4852095/1712016

You can update rental_date and last_update field with null value or you can query like this.

INSERT INTO rental (rental_date, last_update, return_date)
VALUES(NOW(),NOW(),NULL)

or

INSERT INTO rental (rental_date, last_update, return_date)
VALUES(NULL,NULL,NULL)

Answer 4

You have to SELECT now(), you can't use a values clause:

INSERT INTO rental
  (rental_date, last_update, return_date) 
SELECT NOW(), NOW(), NULL;