CODEWITHSUNDEEP
mysqlsql
Josiah
Josiah

Reputation: 3326

SQL: Inserting multiple rows into a table with a static value

Basically, I have some code that looks like this:

while exists (select * from table1 where idForeignkey1 = inidForeignkey2) do

    set var1 = (select idForeignkey2 from table1 where idForeignkey1 = inidForeignkey2 limit 1);

    delete from table1 where idForeignkey2 = var1;

So far so good, just a simple little foreach loop that deletes rows from a table. The problem comes after that. I have a select statement likes this:

select idForeignkey1 from table1 where idForeignkey2 = inidForeignkey2

That can return anywhere between 1 and infinite values

what I need to do is insert multiple rows into table1, using var1 as idForeignkey2 and using values from that select statement as idForeignkey1. For instance, if the select statement returned 1,2,3,4 and var1 = 6, it should insert this into the table:

idForeignkey1    idForeignkey2
     1                 6
     2                 6
     3                 6
     4                 6

Upvotes: 0

Views: 1243

Answers (1)

bobs
bobs

Reputation: 22184

You can try something like this. One statement should do it.

INSERT INTO table1
SELECT idForeignkey1, 6
FROM table1
WHERE idForeignkey2 = inidForeignkey2

Upvotes: 2

Related Questions