insert same fixed value (date) in N number of rows that do not currently exist

Question

My question is similar to this, however, the difference being I want to insert the same fixed date in a date column in N number of rows that currently do not exist in the table.

So:

update mytable
set date = <value>

But execute the above query N number of times.

The restriction on the value to insert is max(date) + 1 month. The date values in the table are the first of every month. If the current date is 9/1/2015, then I want to insert 10/1/2015, N number of times, where N is dependent on another query that I will not get into details here to keep it simple.

Update:

Table Before:

Date, City, Temperature, DewPoint, Blah, Blah, Blah
9/1/2015, Boston, 88, 72
9/1/2015, New York City, 85, 75
9/1/2015, Miami, 95, 77
...
...
...

Where N = number of cities that I am tracking.

After:

Date, City, Temperature, DewPoint....
9/1/2015, Boston, 88, 72
9/1/2015, New York City, 85, 75
9/1/2015, Miami, 95, 77
...
...
...
10/1/2015, NULL, NULL, NULL
10/1/2015, NULL, NULL, NULL
10/1/2015, NULL, NULL, NULL
...
...
...

I'm looking for 1-2 lines of sql code that achieves my objective. I do not want to write inefficiently large number of lines of code to solve what seems to me to be a simple problem.

So you want to run `INSERT INTO mytable(date) VALUES()` 2000 times? — Kenney, Sep 20 '15 at 18:16
Can you provide any restriction on which this value is to be updated? — hjpotter92, Sep 20 '15 at 18:25
Can you give a (very short) listing of the table contents before and after applying the query for N is, say, 2? — Kenney, Sep 20 '15 at 18:35

score 0 · Answer 1 · answered Sep 20 '15 at 19:09

Create a stored procedure as follows:

DELIMITER //
CREATE PROCEDURE MyInsert( IN SomeNumber INT )
BEGIN
    SET @Count = 1;
    SET @NewDate = (SELECT DATE_ADD( MAX(date), INTERVAL 1 MONTH ) FROM yourTable);
    WHILE( @Count <= SomeNumber ) DO
        INSERT INTO yourTable (date)
        VALUES( @NewDate );
        SET @Count = @Count + 1;
    END WHILE;
END//

Now, to insert N rows, call this procedure in MySQL as follows:

CALL MyInsert(30);

You can see it applied here on a fiddle.

insert same fixed value (date) in N number of rows that do not currently exist

1 Answers1