CODEWITHSUNDEEP
sql-serverselectinsertwhere-clause
user1660858
user1660858

Reputation: 23

SQL error when using select into where not exists query

I'm trying to insert a table into another table,

insert into list2([PHONE])
select [column 0] + [column 1] as PHONE
from [list1]
where not exists (select * from list2
where list2.phone = [list1].phone)

but I'm getting an error:

Msg 207, Level 16, State 3, Line 1 Invalid column name 'phone'.

On list2, there are 2 columns, phone and area code. phone has the full phone number and area code has the area code.

On list1, there are also 2 columns, column 0 which has the area code and column 1 which is the first 6 digits of the phone number.

What am I doing wrong?

Upvotes: 2

Views: 843

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You're not allowed to reference column aliases you construct in the SELECT list from the WHERE clause:

insert into list2([PHONE])
select [column 0] + [column 1] as PHONE
from [list1]
where not exists (select * from list2
where list2.phone = [list1].[column 0] + [list1].[column 1])

Alternatively, you could place the construction of the phone column into a subquery within the FROM clause:

insert into list2([PHONE])
select PHONE
from (select [column 0] + [column 1] as PHONE from [list1]) l1
where not exists (select * from list2
where list2.phone = l1.phone)

Upvotes: 4

Related Questions