INSERT Multiple with SELECT

Question

i'm trying to use INSERT into with SELECT DISTINCT and this is what i tried so far.

INSERT INTO creature_loot_template VALUES (id, 68910, 0, 100, 1, 1, 0, 1, 1, NULL)
SELECT DISTINCT id FROM creature WHERE map = 389;

So this is example of what it should do (there is more than 20 - 40 lines:

INSERT INTO creature_loot_template VALUES (123, 68910, 0, 100, 1, 1, 0, 1, 1, NULL);
INSERT INTO creature_loot_template VALUES (124, 68910, 0, 100, 1, 1, 0, 1, 1, NULL);
INSERT INTO creature_loot_template VALUES (125, 68910, 0, 100, 1, 1, 0, 1, 1, NULL);
INSERT INTO creature_loot_template VALUES (125, 68910, 0, 100, 1, 1, 0, 1, 1, NULL);

I've also tried the following SQL's :

INSERT INTO creature_loot_template VALUES ((SELECT DISTINCT id FROM creature WHERE map = 389), 68910, 0, 100, 1, 1, 0, 1, 1, NULL);

SET
@id = (SELECT DISTINCT id FROM creature WHERE map = 389);
INSERT INTO creature_loot_template VALUES (@id, 68910, 0, 100, 1, 1, 0, 1, 1, NULL);

INSERT INTO creature_loot_template SELECT DISTINCT id, 68910, 0, 100, 1, 1, 0, 1, 1, NULL FROM creature WHERE map = 389; — jarlh
– jarlh, Commented Jan 30, 2020 at 12:38
Does this answer your question? sql insert into table from select without duplicates (need more then a DISTINCT) — Prateik Darji
– Prateik Darji, Commented Jan 30, 2020 at 12:39
Yes that does help, i have saw already but i wasn't sure on how it should work but now i did so thanks — Jada Developer
– Jada Developer, Commented Jan 30, 2020 at 12:43

Gordon Linoff · Accepted Answer · 2020-01-30 12:38:20Z

2

Just list the values you want on the SELECT:

INSERT INTO creature_loot_template
    SELECT DISTINCT id, 68910, 0, 100, 1, 1, 0, 1, 1, NULL
    FROM creature
    WHERE map = 389;

I would recommend that you list all the columns in the INSERT as well. This ensures that the code does what you actually want.

answered Jan 30, 2020 at 12:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jada Developer Over a year ago

I hope you didn't do the downvote, else your answer has solved my issue i'll accept whenever i can thank you

Gordon Linoff Over a year ago

@JadaDeveloper . . . As someone who never downvotes, you can be assured that I did not downvote. I find that comments are more useful. And in cases where quality is really low, I just vote to close.

Rick James Over a year ago

DISTINCT is probably redundant.

Gordon Linoff Over a year ago

@RickJames . . . There is no indication in the question of whether there could be duplicates for a single map. I do agree that it is probably redundant, but the OP has it in the question. That comment should really be directed to the OP.

Collectives™ on Stack Overflow

INSERT Multiple with SELECT

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related