I wish to delete records found with function MINUS from file1, which is a result of records not found in file2

select * from file1 a
where not exists (select * from file2 b where a.key=b.key)

delete * from file1 a
where not exists (select * from file2 b where a.key=b.key)

How does this answer the question? Where is MINUS in your statements?

Regards
Michel

We are still waiting for what Blackswan asked and for your Oracle version.

Regards
Michel

Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.

DELETE
FROM file1 y
WHERE EXIST (SELECT *
             FROM (SELECT a.col1, a.col2, a.col3, a.col4
                   FROM file1 a
                   MINUS
                   SELECT b.col1, b.col2, b.col3, b.col4
                   FROM file2 b
                  ) z
             WHERE z.col1 = y.col1 
             AND z.col2 = y.col2 
             AND z.col3 = y.col3 
             AND (z.col4 = y.col4
                  OR (z.col4 IS NULL AND y.col4 IS NULL ))

10g is NOT a version, it is a marketing name.
10.2.0.4.0 is a version number.

You forgot to read:
Quote:

Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.

So is your problem, "how to express the DELETE statement with MINUS?" or "how to get delete faster?"?
If you don't post your actual question, you will not have the answer for it.

Regards
Michel

Missing bracket at the end.

cookiemonster wrote on Thu, 02 December 2010 12:15

Missing bracket at the end.

that what a typo in toad it returns that error.

If it's possible to express the delete statement with MINUS then I would like to know the syntax.

If it's possible to express the delete statement with MINUS then I would like to know the syntax.

sorry about that.

i tried to tell you that is was a type error, missed the bracket in my message but not in TOAD. (That's where i fire my queries)

The problem is that I'm using internet via a citrix farm (data is not accessible from this citrix server) and company regulation does not allow code or data to be send out.

kingman wrote on Thu, 02 December 2010 11:41

The problem is that I'm using internet via a citrix farm (data is not accessible from this citrix server) and company regulation does not allow code or data to be send out.

So post dummy tables and dummy data. We probably only need a small handful of lines to recreate your exact scenario.

DELETE
FROM file1 
WHERE rowid in 
       (SELECT rowid
        FROM file1 a
        WHERE (a.col1, a.col2, a.col3, a.col4) IN
              (SELECT a.col1, a.col2, a.col3, a.col4
               FROM file1 a
               MINUS
               SELECT b.col1, b.col2, b.col3, b.col4
               FROM file2 b
              )
/

Regards
Michel

i commented this line as it's returning an error 'invalid relational operator' (SQL Plus aswell0

If that doesn't work, run the query in sqlplus (if you've got toad you've got sqlplus) and copy and paste the full execution here. SQLplus is a lot better at highlighting the source of errors than TOAD is.

SQL> SELECT 1 b FROM dual d 
  2  WHERE 'A' IN (SELECT 'A' a FROM dual t WHERE t,a = d.b);
WHERE 'A' IN (SELECT 'A' a FROM dual t WHERE t,a = d.b)
                                              *
ERROR at line 2:
ORA-00920: invalid relational operator

How does this answer the question? Where is MINUS in your statements?

drop table file1;
drop table file2;

create table file1 (col1 char(5), col2 number(3));

insert into file1 values('STATE', 2);
insert into file1 values('STATA', 2);
insert into file1 values('STATS', 2);
insert into file1 values('ESTAT', 2);
insert into file1 values('TASTE', 2);
insert into file1 values('EAAST', 2);
insert into file1 values('SEATS', 2);
insert into file1 values('ESATA', 2);
insert into file1 values('ETATE', 2);

create table file2 (col1 char(5), col2 number(3));

insert into file2 values('GUEST', 2);
insert into file2 values('GATES', 2);
insert into file2 values('STAGE', 2);
insert into file2 values('EAAST', 2);
insert into file2 values('SEATS', 2);
insert into file2 values('ESATA', 2);
insert into file2 values('ETATE', 2);

commit;

select * from file1 a
where not exists (select * from file2 b where a.col1=b.col1)
order by col1;

COL1        COL2
----- ----------
ESTAT          2
STATA          2
STATE          2
STATS          2
TASTE          2

5 rows selected.

delete from file1 a
where not exists (select * from file2 b where a.col1=b.col1);

5 rows deleted.

rollback;

select * from file1 a
where exists (
select * from file1 b where a.col1=b.col1
minus
select * from file2 b where a.col1=b.col1)
order by col1;

COL1        COL2
----- ----------
ESTAT          2
STATA          2
STATE          2
STATS          2
TASTE          2

5 rows selected.

delete from file1 a
where exists (
select * from file1 b where a.col1=b.col1
minus
select * from file2 b where a.col1=b.col1);

5 rows deleted.

Results are same. And Hence no need to use minus qry.
SO, MINUS Query can be avoided.