mysqlsql

table is specified twice both as a target for INSERT and as separate source of data


I made this query but it gave me error just like in the title

INSERT INTO data_waktu_vaksinasi (id_binatang, id_vaksin, tanggal_vaksin, status_vaksin) VALUES 
    (1, 1, (SELECT DATE_ADD(max(tanggal_vaksin), INTERVAL 1 YEAR)) FROM data_waktu_vaksinasi, 'belum')

Solution

  • MySQL does allow the same table to be used for the source and target for inserts. You just need to use the correct syntax:

    INSERT INTO data_waktu_vaksinasi (id_binatang, id_vaksin, tanggal_vaksin, status_vaksin) 
         SELECT 1, 1, DATE_ADD(max(tanggal_vaksin), INTERVAL 1 YEAR), 'belum'
         FROM data_waktu_vaksinasi;