0

Having the following inputs which aren't taken from any other query...

(891,892,903,993,1008)

I wanto to make for each one, (891, 892, etc.) the following INSERT (being XXXX the actual value)

INSERT INTO myTable (id, name)
SELECT
    id,
    name
FROM myTable
WHERE exp_id = XXXX AND cntr_id <> (SELECT id from plnt where id_emp = XXXX);

How can I make a function to make all of them in a row instead of doing one by one as follows?

WHERE exp_id = 891 AND cntr_id <> (SELECT id from plnt where id_emp = 891);
and then
WHERE exp_id = 892 AND cntr_id <> (SELECT id from plnt where id_emp = 892);
etc.etc.

Thanks in advance.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
Avión
  • 7,963
  • 11
  • 64
  • 105

1 Answers1

1

You don't need a function (and they don't work like you think they do). But you can join the table to eliminate the subquery.

You can rewrite your query like this:

INSERT INTO myTable (id, name)
SELECT
    id,
    name
FROM your_table /*you were missing the from clause btw*/
LEFT JOIN plnt ON your_table.exp_id = plnt.id_emp 
WHERE exp_id IN (891,892,903,993,1008) AND cntr_id <> plnt.id 
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Is the `JOIN` correct? I'm not getting the same results from the original query and this one, testing with the value `891` for example. – Avión Jul 13 '17 at 13:40
  • It works! but I think I've got an error in my code. The thing is that my subquery `SELECT id from plnt where id_emp = XXXX` sometime returns more than 1 row (I've just noticed it). Is there any way to adapt your code so he can work when the subquery returns more than 1 row? Thanks in advance. – Avión Jul 13 '17 at 14:20
  • Change it to `and cntr_id not in (your_subquery)`. This allows multiple values, `<>` does not. Or when your subquery returns the same result multiple times, you can add `DISTINCT` in the select of your subquery. – fancyPants Jul 13 '17 at 14:45
  • How would that be with your answer? I mean, without using my subquery. Thanks in advance! – Avión Jul 13 '17 at 14:51
  • This problem doesn't occur with my query at all. When there are multiple rows in plnt for each exp_id, it doesn't matter. Or I misunderstood you. Just execute the select, leave the insert out, then you'll see what it does. – fancyPants Jul 13 '17 at 15:05