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

Question

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')

Doesn't this work? `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')` — Phiter, Jan 17 '16 at 15:12
@PhiterFernandes i've tried to run it on phpmyadmin, but it gave me error that the table is specified twice — Lia Dianti, Jan 17 '16 at 15:22

score 13 · Accepted Answer · answered Jan 17 '16 at 15:28

13

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;

answered Jan 17 '16 at 15:28

Gordon Linoff

1,242,037
58
646
786

@LiaDianti . . . I don't understand the question. Perhaps you should ask another question with example data to explain what you want. – Gordon Linoff Jan 17 '16 at 23:20
so I want to insert multiple data at the same time with this query, like this INSERT INTO data_waktu_vaksinasi (id_binatang, id_vaksin, tanggal_vaksin, status_vaksin) VALUES ('$id', 1, SELECT 1, 1, DATE_ADD(max(tanggal_vaksin), INTERVAL 1 YEAR), '$status'), ('$id', 2, SELECT 1, 1, DATE_ADD(max(tanggal_vaksin), INTERVAL 1 YEAR), '$status'), ('$id', 3, SELECT 1, 1, DATE_ADD(max(tanggal_vaksin), INTERVAL 1 YEAR), '$status') but it still gave me error – Lia Dianti Jan 18 '16 at 06:26

score 3 · Answer 2 · answered Jan 17 '16 at 15:10

3

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

answered Jan 17 '16 at 15:10

Pரதீப்

91,748
19
131
172

Alas, this answer is not correct. MySQL does not permit references to the same table for `update` and `delete`. It does permit references to the same table for `insert`. – Gordon Linoff Jan 17 '16 at 15:28
Oops, I should add the code *is* correct, just overly complicated. – Gordon Linoff Jan 17 '16 at 16:09

ArchLinuxTux · Answer 3 · 2018-04-19T07:50:50.937

Try this code

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

I didn't test this code, but I tested an equivalent code (also an INSERT INTO statement with same table in subquery as in master-query)

The trick

is the wrap the subquery with the same table into another subquery, that only queries the columns from the SELECT-clause of the actual subquery.

Result: Mysql doesn't complain

I don't know why this works
It seems to me kina like cheating

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

3 Answers3

Linked