Insert INTO Fails ???

Question

Post reply

Insert INTO Fails ???

JN-161845

SSCrazy

Points: 2052
More actions
February 17, 2005 at 1:59 pm

#166030

Hello,
Why does the following Insert INTO statement inserts NO records?
*********************************
INSERT INTO Prop
(ListingID, Address, City, Zip, ListPrice, PropCID, PropTID)
SELECT
LSID, B.Address, B.City, B.Zip, B.Price,
PropCID = (CASE B.PropID WHEN 'A' THEN 'AA' WHEN 'B' THEN 'BB' WHEN 'C THEN 'CC' WHEN 'D' THEN 'DD' WHEN 'E' THEN 'EE'
WHEN 'F' THEN 'FF' ELSE 'GG' END),
PropTID = (CASE B.PropTID WHEN 'A' THEN 'A1' WHEN 'B' THEN 'B2' WHEN 'C' THEN 'C2' WHEN 'D' THEN 'D2' WHEN 'E' THEN 'E2'
WHEN 'F' THEN 'F2' ELSE 'G2' END)
FROM PropMaster A, tmp_Listings B
WHERE A.ListingID <> B.LSID
*********************************
If I remove the PropMaster A reference in FROM clause and remove the WHERE condition then it inserts all the records, but not if I leave it as it is above. Does anyone know why this happens or how I can rewrite it to achieve what I am looking for? Basically, I want to insert records if they are not found in the table to avoid duplicates. If there is a different way of doing this, please suggest.
Thanks.
JN

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Change:

FROM PropMaster A, tmp_Listings B

WHERE A.ListingID <> B.LSID

to:

FROM tmp_Listings B left join PropMaster A on A.ListingID = B.LSID

WHERE A.ListingID IS NULL

* Noel

JN-161845 SSCrazy Points: 2052 More actions · Answer 2

JN-161845

SSCrazy

Points: 2052

February 17, 2005 at 2:11 pm

#542949

Thanks!! That worked!

JN

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Instead of a Join, testing left joined table as null, why not a sub-query ?

FROM PropMaster A Where A.ListingID NOT IN ( Select LSID FROM tmp_Listings)

noeld SSC Guru Points: 96590 More actions · Answer 4

You may want to change the "NOT IN" for a "NOT EXISTS". "NOT IN" tends to be dagerous

* Noel

JN-161845 SSCrazy Points: 2052 More actions · Answer 5

If I use NOT Exists, I get SQL-DMO(SQLState: 42000) SQL error 156: Incorrect syntaxt near the keyword Exists.

If I use FROM PropMaster A Where A.ListingID NOT IN ( Select LSID FROM tmp_Listings)

Then I get "Invalid Column Name" PropTID, which is found in tmp_Listings table. But I use LEFT JOIN then get no error regarding columns not found and get all records also.

Thanks.

JN

noeld SSC Guru Points: 96590 More actions · Answer 6

JN,

The NOT EXISTS is meant to be used as:

FROM PropMaster A

Where NOT EXISTS ( Select * FROM tmp_Listings B Where B.LSID = A.ListingID )

HTH

* Noel

JN-161845 SSCrazy Points: 2052 More actions · Answer 7

Thanks, but I still get

"Invalid Column Name" PropTID

That column is found in tmp_Listings table.

But if I use LEFT JOIN then I get no such error regarding columns not found and get all records also.

rhunt SSCertifiable Points: 5868 More actions · Answer 8

JN, are you getting the "Invalid Column Name" PropTID error on the "IN" clause or the "EXISTS"?

For the "IN" clause, don't you still need to join your "B" table since you are selecting from it?

For the "EXISTS" I would expect this to work. I don't use "EXISTS" much but I think the where clause in the EXISTS statement creates a de facto join.

Anyone want to clarify?

noeld SSC Guru Points: 96590 More actions · Answer 9

JN,

I did that as an example, for the real deal you to get the data from tmp_listing not from Propmaster like it was on the example if you use:

FROM tmp_Listings B

Where NOT EXISTS ( Select * FROM PropMaster A Where B.LSID = A.ListingID )

OR

FROM tmp_Listings B Where B.LSID NOT IN ( Select ListingID FROM PropMaster)

you are not going to get the error!

HTH

* Noel

JN-161845 SSCrazy Points: 2052 More actions · Answer 10

Both IN clause and EXISTS clause are giving same error if I copy the statement the way it's written in the post. I will see if I need to modify it.

Thanks.