nicker nicker - 3 months ago 7
MySQL Question

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

Answer

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);
Comments