Search code examples
mysqlinsertinsert-into

INSERT INTO SELECT - replace one field with NULL

I'm trying to perform an INSERT INTO SELECT statement, but while I'm doing it I would like one of the fields within the SELECT statement to return NULL rather than what's in there originally.

So far I have:

INSERT INTO `archive`
    SELECT *
    FROM `product`
    WHERE `id` = ?

I thought about using the following, which doesn't work as it adds an extra field rather than replace the existing one:

INSERT INTO `archive`
    SELECT *, '' AS `image`
    FROM `product`
    WHERE `id` = ?

I know I could list all of the fields in order to do it, but that would mean I have to update this statement every time I add new field etc.

Is there a way of doing it with the short version as above?

Solution

  • No. Your two choices are:

    1. List the fields individually in the SELECT statement
    2. Do an UPDATE after the INSERT to re-set the column to NULL

    The first is the proper solution, IMO. It makes future maintenance easier (instead of more difficult, as it appears), and makes it much clearer when you (or a new programmer) visits the code 6 months from now.