2

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')
Boris D. Teoharov
  • 2,319
  • 4
  • 30
  • 49
  • Do you want to insert one row or many? – ypercubeᵀᴹ Oct 05 '12 at 17:08
  • I want to insert multiple rows not a single one. – Boris D. Teoharov Oct 05 '12 at 17:09
  • You say you know the ID related to the values but this ID is not found in T1. Where is it found then? – ypercubeᵀᴹ Oct 05 '12 at 17:13
  • And why would you want to insert multiple identical rows into a table? – ypercubeᵀᴹ Oct 05 '12 at 17:14
  • I didnt explain it properly. Rows are not identical but they are contained neither in t1 nor in t2. – Boris D. Teoharov Oct 05 '12 at 17:16
  • 1
    Can you please edit the question and put some sample rows of both tables and sample rows you want to insert? Otherwise, it's more a guess game of what you mean. Also, clarify if it's for MySQL or for SQL-Server. – ypercubeᵀᴹ Oct 05 '12 at 17:18
  • I encountered a situation where I DID need to generate rows. Joining cookie data (clicks and events) from an AdServer. Sometimes the Click is not present, but still need to be able to report on Event, therefore requiring the existence of a click. Dirty but true. Logic I used for this in answer I posted below. With these comments, not exactly the same but should be easy to convert – Pete Carter Oct 05 '12 at 17:22

1 Answers1

3
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
Pete Carter
  • 2,691
  • 3
  • 23
  • 34
  • 1
    Joins the 2 tables based on ID. Where there is no join, creates a row and inserts a constant, in this case -1 into T2 – Pete Carter Oct 05 '12 at 17:29
  • Very interesting. Thank you! However, isnt there a better way...maybe just using nested queries or ..?? – Boris D. Teoharov Oct 05 '12 at 17:32
  • 1
    The `SELECT ISNULL(T1.ID,-1)` is equivalent to `SELECT -1` for the specific query. – ypercubeᵀᴹ Oct 05 '12 at 17:36
  • 1
    Joins typically out perform sub queries. When I did this I evaluated several methods and this was the faster (on my data - which was v large tables) – Pete Carter Oct 05 '12 at 17:36
  • how woud the query look like if you insert some data into some other columns in T1, and not the ID column at all – Boris D. Teoharov Oct 05 '12 at 17:43
  • 1
    @dudelgrincen - I have updated the query to represent this. This is the whole advantage of the ISNULL() function. I typed the original answer on a phone on a train, so perhaps didn't fully illustrate the technique – Pete Carter Oct 05 '12 at 19:37