Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

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')
like image 623
Lia Dianti Avatar asked Jan 17 '16 15:01

Lia Dianti


2 Answers

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;
like image 176
Gordon Linoff Avatar answered Oct 20 '22 00:10

Gordon Linoff


Put the source table in subquery to fix this problem. Mysql does not allow same table in source and target for DML operation's

INSERT INTO data_waktu_vaksinasi 
            (id_binatang, 
             id_vaksin, 
             tanggal_vaksin, 
             status_vaksin) 
SELECT 1, 
       1, 
       dt, 
       'belum' 
FROM   (SELECT Date_add(Max(tanggal_vaksin), interval 1 year) AS dt 
        FROM   data_waktu_vaksinasi)a 
like image 40
Pரதீப் Avatar answered Oct 20 '22 00:10

Pரதீப்