SQL What am I doing wrong ? 
Author Message
 SQL What am I doing wrong ?

I have two tables:

[ORG]

ID ID2  Name  
--  ---    --------
01 AA  James
02 AA  Kevin

[TMP]

ID ID2  Name  
--  ---    --------
01 AA  James
02 BB  June

ID1 and ID2 are unique and a key.

What I like to do is to update the ORG table with the new records in
the TMP table.

What I have tried without success is:

INSERT INTO ORG
SELECT * FROM [TMP] WHERE (ID,ID2) NOT IN
SELECT (ID,ID2) FROM [ORG]

I cannot even get the select statments to work.What am I doing wrong?
Any Help would be appreciated.
Morten Brun

http://www.***.com/



Mon, 01 Mar 1999 03:00:00 GMT
 SQL What am I doing wrong ?

Quote:

>I have two tables:
>[ORG]
>ID ID2  Name  
>--  ---    --------
>01 AA  James
>02 AA  Kevin
>[TMP]
>ID ID2  Name  
>--  ---    --------
>01 AA  James
>02 BB  June
>ID1 and ID2 are unique and a key.
>What I like to do is to update the ORG table with the new records in
>the TMP table.

Try this:

INSERT INTO Org ( ID1, ID2 )
SELECT DISTINCTROW Tmp.ID1, Tmp.ID2
FROM Tmp, Org
WHERE ((Tmp.ID1 Not In (Select ID1 From ORG)));

I believe this will work.  Note you cannot use the * with a criteria
statement in an Insert Into.

HTH.

Rick Clark



Mon, 01 Mar 1999 03:00:00 GMT
 SQL What am I doing wrong ?

Quote:

> I have two tables:

> [ORG]

> ID ID2  Name
> --  ---    --------
> 01 AA  James
> 02 AA  Kevin

> [TMP]

> ID ID2  Name
> --  ---    --------
> 01 AA  James
> 02 BB  June

> ID1 and ID2 are unique and a key.

> What I like to do is to update the ORG table with the new records in
> the TMP table.

> What I have tried without success is:

> INSERT INTO ORG
> SELECT * FROM [TMP] WHERE (ID,ID2) NOT IN
> SELECT (ID,ID2) FROM [ORG]

> I cannot even get the select statments to work.What am I doing wrong?
> Any Help would be appreciated.
> Morten Brun

> http://www.novo.dk

I depends on the database engine. (even the version!)
My database (SQLBase) sometimes try to execute the 2nd select after each record inserted, then "fail"
because now the record is there (Weird huh!).
I've got some work arounds for SQLBase. ie. (if TMP is truly temporary...

Delete from TMP WHERE (ID,ID2) IN
(SELECT (ID,ID2) FROM ORG)

then

Insert into ORG
Select * from TMP

Let me know if I can help more.
--
Thomas Hugel
Brown Distributing Co. (Budweiser)
1330 Allendale Rd.
West Palm Beach, FL 33405



Mon, 01 Mar 1999 03:00:00 GMT
 SQL What am I doing wrong ?

Quote:

> I have two tables:

> [ORG]

> ID ID2  Name
> --  ---    --------
> 01 AA  James
> 02 AA  Kevin

> [TMP]

> ID ID2  Name
> --  ---    --------
> 01 AA  James
> 02 BB  June

> ID1 and ID2 are unique and a key.

> What I like to do is to update the ORG table with the new records in
> the TMP table.

> What I have tried without success is:

> INSERT INTO ORG
> SELECT * FROM [TMP] WHERE (ID,ID2) NOT IN
> SELECT (ID,ID2) FROM [ORG]

> I cannot even get the select statments to work.What am I doing wrong?
> Any Help would be appreciated.
> Morten Brun

> http://www.novo.dk

I deppends on the database engine. (even the version!)
My database (SQLBase) sometimes try to execute the 2nd select after each record inserted, then "fail"
because now the record is there (Weird huh!).
I've got some work arounds for SQLBase. ie. (if TMP is truly temporary...

Delete from TMP WHERE (ID,ID2) IN
(SELECT (ID,ID2) FROM ORG)

then

Insert into ORG
Select * from TMP

Let me know if I can help more.
--
Thomas Hugel
Brown Distributing Co. (Budweiser)
1330 Allendale Rd.
West Palm Beach, FL 33405



Mon, 01 Mar 1999 03:00:00 GMT
 SQL What am I doing wrong ?

Quote:

> I have two tables:

> [ORG]

> ID ID2  Name
> --  ---    --------
> 01 AA  James
> 02 AA  Kevin

> [TMP]

> ID ID2  Name
> --  ---    --------
> 01 AA  James
> 02 BB  June

> ID1 and ID2 are unique and a key.

> What I like to do is to update the ORG table with the new records in
> the TMP table.

> What I have tried without success is:

> INSERT INTO ORG
> SELECT * FROM [TMP] WHERE (ID,ID2) NOT IN
> SELECT (ID,ID2) FROM [ORG]

> I cannot even get the select statments to work.What am I doing wrong?
> Any Help would be appreciated.
> Morten Brun

> http://www.novo.dk

The sub-query for the IN clause must return a SINGLE column.  You can't
run it because as-written it is syntactically and semantically
incorrect.

So, you would have to do some kind of combining thingie, like:

 INSERT INTO ORG
 SELECT * FROM [TMP] WHERE ID || ID2 NOT IN
 SELECT (ID || ID2) FROM [ORG]

Note: I *think* the || means concatenate, but you should check that.

This is, of course, a very bad idea if ORG is very large.

------------------------------------------------------------
Richard Berman                       Vox: (213) 258-7525
Software Technology Service          Fax: (213) 258-9483
3737 Division Street, Los Angeles, CA 90065

------------------------------------------------------------



Tue, 02 Mar 1999 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. ADO and SQL: what am I doing wrong ?

2. What am I doing wrong (RTRIM, PL/SQL)

3. What am I forgetting or doing wrong

4. what am I doing wrong in this procedure?

5. TSQL question - what am I doing wrong?

6. what am i doing wrong?

7. Simple one what am i doing wrong

8. Darn what am i doing wrong?

9. What am I doing wrong

10. What am I doing wrong?

11. Joins - What I'am doing wrong

12. I think I am doing something wrong


 
Powered by phpBB® Forum Software