CODEWITHSUNDEEP
mysql
nicker
nicker

Reputation: 487

Insert with multiple select statement if not exist

I'd tried insert with multiple statement with the code below

insert into peoplePos
select a.name,b.option
FROM (SELECT name from people t1) a
JOIN (SELECT option FROM optionTable WHERE name = 'Position') b
where not exists (select * from peoplePos t2 where t2.name = t1.name);

However i got this error

Error Code: 1054. Unknown column 't1.name' in 'where clause'

It seems in the where clause, t1 cant access the people t1 declared earlier

I'd tried using a.t1.name and people.name, both doesn't work

Is there away to access it? Thanks

Upvotes: 0

Views: 64

Answers (1)

AntDC
AntDC

Reputation: 1917

I'd think you'd want some conditions on your join, but for what you ask, t1 does not exist. You have aliased it as a so use a.

    insert into peoplePos
    select a.name,b.option
    FROM (SELECT name from people t1) a
    JOIN (SELECT option FROM optionTable WHERE name = 'Position') b ON "SOME CONDITION OR OTHER"
    where not exists (select * from peoplePos t2 where t2.name = a.name);

Upvotes: 1

Related Questions