Search code examples
phpmysqlsqlinsertion

SQL: insert values in one table if given ID was not found in another (using single query)


I have two tables on MySQL server: T1 and T2. ID is my primary key for T1 and ID is foreign key for T2.

My question is : How could I insert some values in T2 (using single query) if I know the ID related to them (values) but only in case this ID was not found in T1?

T1
id,c1,c2,c3,many other columns
example id:'fry54632' //yes, it is not numerical if matters

T2
id,cc1,cc2,cc3,few other columns
example id:'fry54632', cc1,cc2,cc3... have nothing in common with c1,c2,c3

myDataSource:
countains id, which is same for T1 and T2 and contains some other data that should be
inserted in T2 but not in T1

I think i should use something like this but I am not sure:

insert into t2 (col1,col2,col3)
select 'const1','const2','const3'
from t1 where not exists 
(select id from t1 t --...t1, I mean: insert 'const1'...and etc strings in t2 if
--specified ID was not found in t1}
where t.id='someID but not t2.id')

Solution

  • INSERT INTO T1 (ID, Col1, Col2)
    SELECT ISNULL(T1.ID,-1), ISNULL(T1.Col1,'Arbatory Value'), ISNULL(T1.Col2,'Arbatory Value')
    FROM T2
    LEFT OUTER JOIN T1
    ON T2.ID = T1.ID
    WHERE T1.ID IS NULL