CODEWITHSUNDEEP
mysqlsqlselect
Alexey
Alexey

Reputation: 3637

Create records duplicates mysql

I am creating a weekly timetable for an institution and one of the options is copying the timetable to next week (if, let's say, only 1 lesson changes).

Is there a way to do this with one query? Something like :

Duplicate * from school_entries Where `date` BETWEEN DATE('$date') AND DATE_ADD(DATE('$date'), INTERVAL 6 DAY) Set `date` = `date' + 7;

Upvotes: 1

Views: 41

Answers (2)

George G
George G

Reputation: 7695

INSERT INTO school_entries (`date`,`secondfield`,...)
SELECT 
    `date` + 7 AS `date`,
    `secondfield`,
    ...
    /** 
     * YOU HAVE TO MANNUALLY SELECT ALL REQUIRED FIELDS
     * EXCEPT PRIMARY KEY, OR IF THERE IS ANY UNIQUE FIELDS
     * OTHERISE YOU'LL GET DUPLICATE ENTRY ERROR 
     */
FROM school_entries   
WHERE `date` BETWEEN DATE('$date') AND DATE_ADD(DATE('$date'), INTERVAL 6 DAY)

Upvotes: 1

George Cummins
George Cummins

Reputation: 28906

Use an INSERT...SELECT statement. The SELECT statement should return all of the unchanged fields, and you can supply the new dates and other values the the place of date_to_be_modified:

INSERT INTO school_entries
    date_to_be_modified AS date,
    (
        SELECT 
            other_fields,
            as_needed,
        FROM school_entries
        WHERE date BETWEEN start_date AND end_date
    )

Upvotes: 3

Related Questions