SQL Insert into (Select from)

Question

table 1

ID             Qty      completed_qty   KEY
1              2          1              a
2              3          1              b
3              4          3              c

table 2

ID             Qty      completed_qty   Percent     Priority         KEY
 1              2            1            50          H               a
 2              3            1           33.33        L               b
 3              4            3            75          H               c

I has 2 table which I select table 1 and then insert into table 2 using script below

INSERT into table2(Qty, Completed_qty, Percent, KEY)
select Qty, Completed_qty, (completed_qty / Qty) * 100 [Percent], KEY from table1 where
KEY not in (select table2.KEY from table2)

So, when everytime I perform the script, the same record will not copy into table2.
I face a problem when the Percentage from the table1 is change, I want update also the Percent in table 2, I change the script as below but cannot make it.

INSERT into table2(Qty, Completed_qty, Percent, KEY)
select Qty, Completed_qty, (completed_qty / Qty) * 100 [Percent], KEY from table1 where 
KEY not in (select table2.KEY from table2)
and (completed_qty / Qty) * 100 <> (select table2.Percent from table2)

I get the error as below:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Any one as done the similar script before. Any idea how I can done this?

score 0 · Answer 1 · answered Nov 20 '14 at 02:38

This is your query:

insert into table2(Qty, Completed_qty, Percent, KEY)
    select Qty, Completed_qty, (completed_qty / Qty) * 100 [Percent], KEY
    from table1
    where KEY not in (select table2.KEY from table2) and
          (completed_qty / Qty) * 100 <> (select table2.Percent from table2)
-----------------------------------------^

The second subquery is the problem. I suspect that you want not in:

insert into table2(Qty, Completed_qty, Percent, KEY)
    select Qty, Completed_qty, (completed_qty / Qty) * 100 [Percent], KEY
    from table1
    where KEY not in (select table2.KEY from table2) and
          (completed_qty / Qty) * 100 not in (select table2.Percent from table2 where table2.Percent is not null)

The additional is not null is important when you use not in. Otherwise a NULL value in table2.Percent would case every row to be filtered out.

Sorry, after change to NOT IN, there is not record been insert — Bubble Bub, Nov 20 '14 at 03:26

Rahul · Answer 2 · 2014-11-20T04:34:07.173

0

You can use a NOT IN clause there as well saying

and (completed_qty / Qty) * 100 NOT IN (select table2.Percent from table2)

EDIT:

You can probably insert NULL in Percent column like below

INSERT into table2(Qty, Completed_qty, Percent, KEY)
select Qty, 
Completed_qty, 
null, 
KEY 
from table1 
where KEY not in (select table2.KEY from table2)

Later then do an UPDATE

UPDATE table2 a 
    JOIN table1 b ON a.key = b.key 
SET a.Percent = (b.completed_qty / b.Qty) * 100
WHERE a.Percent IS NULL

edited Nov 20 '14 at 04:34

answered Nov 20 '14 at 02:39

Rahul

76,197
13
71
125

After change to NOT IN, there is not record been insert – Bubble Bub Nov 20 '14 at 03:26
the purpose is to insert data selected into new table. And the record been insert will not insert again but will need update the percentage. So, using OR will cause the duplicate data? – Bubble Bub Nov 20 '14 at 03:38
@DerickLoo, then you should be doing an `UPDATE` operation. why you mentioned `INSERT` in post? – Rahul Nov 20 '14 at 03:40
My selected data will increase daily. So I want insert the data not been inserted in to table 2 and skip the data already in the table 2 but will update the percent column in the table 2 when the percent selected count from table 1 is change. – Bubble Bub Nov 20 '14 at 03:48
Rahul, thanks for the direction and solutions. I will try it out – Bubble Bub Nov 20 '14 at 06:15

score 0 · Answer 3 · answered Nov 20 '14 at 02:41

0

You're getting the error because the subquery is returning multiple results. You can use NOT IN instead of <> for that.

However, based on your statement, I want update also the Percent in table 2. it sounds like you want to update table2 if table1 gets changed and the key already exists in table2. In that case, you need to use an UPDATE query for that instead:

UPDATE table2 t2
JOIN table1 t1 on t2.key = t1.key
SET Percent = t1.completed_qty / t1.Qty * 100

answered Nov 20 '14 at 02:41

sgeddes

62,311
6
61
83

Hi sgeddes, if my table 2 is empty and is first run, how I can perform it? I need run two different query. First is insert into with select from. Then run second query is update? – Bubble Bub Nov 20 '14 at 02:53
@DerickLoo -- correct, it sounds like you want to insert records that do not exist, and update those that do exist. For that, you can use 2 separate statements, or you could use an `upsert` if that's your primary key (doesn't look like it is though): http://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par – sgeddes Nov 20 '14 at 02:55
There is an incorrect syntax at the JOIN, t2 and at the on. – Bubble Bub Nov 20 '14 at 03:19

score 0 · Answer 4 · answered Nov 20 '14 at 02:52

if the table has much rows, your query will be slow.

try this one :

Insert into table2(Qty, Completed_qty, Percent, KEY)
Select a.Qty, a.Completed_qty, (a.completed_qty / a.Qty) * 100 [Percent], a.KEY 
From table1 a
Left Join table2 b On b.KEY = a.KEY
Where b.KEY is Null
    Or
    ( b.KEY is Not Null and ((a.completed_qty / a.Qty) * 100) <> b.Percent )

SQL Insert into (Select from)

4 Answers4