CODEWITHSUNDEEP
mysqlsql
Lia Dianti
Lia Dianti

Reputation: 149

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

Upvotes: 10

Views: 16027

Answers (4)

LucianDex
LucianDex

Reputation: 647

My two cents:

INSERT INTO some_table
(col1, col2, foo)
VALUES
('col1 val', (SELECT st.id FROM some_table st WHERE foo = "bar"), 'foo val');
-- you can select any column is required instead of st.id

The solution lies in assigning an ALIAS to the second mention of the table (and in future subqueries).

BUT, if you want some data from inserted rows inside the actual query, this will not work; it is the same transaction.

Upvotes: 0

aiutopia.dev
aiutopia.dev

Reputation: 852

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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;

Upvotes: 13

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 3

Related Questions