CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Ashish
Ashish

Reputation: 188

Populating a target table from another table using same target table

I have table A which and there are several other rated tables. I want to do conversion in phase manner. To do the conversion I am first creating a temprary table TMP, from table A (not all the records. Lets say X 

CREATE TABLE TMP (EMP_ID NUMBER(9));

SELECT A.EMP_ID FROM A, TMP WHERE A.EMP_ID <>TMP.EMP_ID

But the select query is not returning any records. I have 1 million records i table. Can someone tell what wrong I am doing.

Upvotes: 0

Views: 48

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

You are using a join syntax that was used decades ago. The comma between your table translates to CROSS JOIN. So you have

SELECT A.EMP_ID 
FROM A
CROSS JOIN TMP 
WHERE A.EMP_ID <> TMP.EMP_ID;

A cross join combines every record in the left table with every record in the right table, so as to get all combinations. Your table TMP is empty, so your result set is also empty. (No matter what WHERE clause.)

Using proper ANSI joins, you can rewrite your query to 

SELECT A.EMP_ID 
FROM A
INNER JOIN TMP ON A.EMP_ID <> TMP.EMP_ID;

which does exactly the same (only it applies the join criteria directly), and your result is still empty of course.

What you are probably looking for is this:

select emp_id from a
where emp_id not in (select emp_id from tmp);

which gets you all emp_id from A that are not (yet) in TMP.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Your query is not returning any records because tmp is empty. When you do a cross join, the number of records you get is the product of the records in the two tables. If one has zero records, then you get zero records out.

So, I would simply suggest:

insert into tmp (emp_id)
    select A.EMP_ID 
    from A;

If you want to do this when tmp is not empty, then you would want additional logic to prevent duplicates.

Upvotes: 0

Related Questions