Sub query with not equal

oracleoracle-10goracle-11g-r2

table_1


col1
SV0362
RU0177CSPG
NO0124CSPG
VN3582
AT9923CSPG
BZ0324
PE0309
AS4293EXPT
ML0331OMKT

table 2

colA
OO56128665
OO56128665
OO56128665
OO56128665
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
OO56128665
OO56128665

I want to compare col1 of table1 with colA of table 2 and return the values which are not qual

something like this

select * from table1 where col1 <> (select colA from table_2 a,table1 b where b.col1=a.colA)

but this would give single row returns more than one subquery

Best Answer

I assume you want rows from table 1 that does not exist in table_2 and vice versa:

SELECT col1, 'table_1' as origin
FROM table_1 x
WHERE NOT EXISTS (
    SELECT 1 FROM table_2 y
    WHERE x.col1 = y.cola
)
UNION
SELECT cola, 'table_2' as origin
FROM table_2 x
WHERE NOT EXISTS (
    SELECT 1 FROM table_1 y
    WHERE y.col1 = x.cola
)

Related Solutions

Simple query with date type requires explicit conversion to_date of date

Your belief that this should not be necessary is correct based on your assumptions. Normally this means the assumptions should be rechecked (unless there is data corruption or a bug). If you run the following you should get the same results from the first query as the second:

DROP TABLE t1;
CREATE TABLE t1 AS (
   SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn 
   FROM dual CONNECT BY level <=120);

select * from t1 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

select myDateColumn from t1 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

To check some of the assumptions, can you show the results of the following query?

SELECT myDateColumn, to_date(myDateColumn)
   , to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY') 
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

The first three columns should all show identical information and the last should verify that the year is correct.

Update field1.table1 based on compound join between table1 and table2 – Oracle

update table1 set flag_column = 1
 where exists (select 1 from table2
                where table1.columnx = table2.columnx
                  and table1.columny = table2.columny)

This will update every row in table 1 where the x, y column combination exists in table2.

Best Answer

Related Solutions

Simple query with date type requires explicit conversion to_date of date

Update field1.table1 based on compound join between table1 and table2 – Oracle

Related Question