getting subquery error in SQL

Question

Trying to insert into some table using my current table . Here is the structure of my current Temptable:

CustomerID  Name        Values      FakeName
1           John        10apples    10apples_20oranges_30bananas
1           John        20oranges   10apples_20oranges_30bananas
1           John        30bananas   10apples_20oranges_30bananas
2           Steve       15apples    15apples_25oranges_35bananas
2           Steve       25oranges   15apples_25oranges_35bananas
2           Steve       35bananas   15apples_25oranges_35bananas
3           Harvey      10apples    10apples_20oranges_30bananas
3           Harvey      20oranges   10apples_20oranges_30bananas
3           Harvey      30bananas   10apples_20oranges_30bananas

This is my peice of code that I am executing :

Insert into customer (FakeName,type,address)
select (select distinct FakeName from Temptable),
        2,
        xyz

 from customer c
 where c.fakename not in (select distinct Fakename from TempTable)

getting following error

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

I want to insert distinct Fakenames from temptable to customer table , making sure if the fake name already exist then not to insert a duplicate fakename

Why are you selecting from the subquery instead of just selecting `c.fakename` itself? — ZLK, Oct 12 '17 at 00:40

score 2 · Accepted Answer · answered Oct 12 '17 at 00:40

2

select distinct FakeName from Temptable is not a scalar, so you can't use it like that.

I think this is what you're after:

Insert into customer (FakeName,type,address)
select distinct 
        FakeName,
        2,
        xyz
 from Temptable c
 where c.fakename not in (select distinct Fakename from customer)

answered Oct 12 '17 at 00:40

Nick.Mc

18,304
6
61
91

I tried this but it is inserting duplicates. Like when i do " select * from customer where FakeName in '10apples_20oranges_30bananas' " , it is giving me 3 outputs with the same FakeName. My expectation is to see only 1 – achillies1987 Oct 12 '17 at 01:43
Distinct removes duplicates. By 'duplicate' do you mean the same FakeName appears twice? that's because column `xyz` has a different value. You need to pick which xyz you want. – Nick.Mc Oct 12 '17 at 01:45
Just run this: `select distinct FakeName from Temptable` Do you see duplicates? – Nick.Mc Oct 12 '17 at 10:49
How about this: `select distinct LTRIM(RTRIM(FakeName) from Temptable` Can you post a screenshot of the duplicates if you still see them – Nick.Mc Oct 13 '17 at 01:14
I mean `select distinct LTRIM(RTRIM(FakeName)) from Temptable` (forgot a bracket) – Nick.Mc Oct 13 '17 at 03:39

score 0 · Answer 2 · answered Oct 12 '17 at 00:55

0

I think you want something like this:

Insert into customer (FakeName, type, address)
    select distinct tt.FakeName, 2, 'xyz'
    from temptable tt
    where not exists (select 1 from customer c where c.fakename = tt.fakename);

Notes:

xyz is undefined in your query (unless it is a column in customer, which seems unlikely).
not in will filter all rows if any name the subquery is NULL. I replaced it with not exists.
I am speculating that you want to avoid duplicate entries in customer, so I changed the subquery.
When using in/not in with a subquery, select distinct is redundant.

answered Oct 12 '17 at 00:55

Gordon Linoff

1,242,037
58
646
786

I tried this but it is inserting duplicates. Like when i do " select * from customer where FakeName in '10apples_20oranges_30bananas' " , it is giving me 6 outputs with the same FakeName. My expectation is to see only 1 – achillies1987 Oct 12 '17 at 02:05
@achillies1987 . . . Perhaps the duplicates look the same, but different by having spaces, hidden characters, or characters that look the same. – Gordon Linoff Oct 12 '17 at 02:11

getting subquery error in SQL

2 Answers2