CODEWITHSUNDEEP
oracle-database
GIS-Jonathan
GIS-Jonathan

Reputation: 4647

Converting a select statement to a delete

I have a kind of metadata table in my database that I want to cull of old records. I have created a "select" statement that selects the rows I want to delete:

select m.tablename, m.OWNERNAME
    from MAPINFO.MAPINFO_MAPCATALOG m
    left outer join sys.ALL_TABLES t
    on TRIM(m.tablename) = t.TABLE_NAME and TRIM(m.OWNERNAME) = t.owner
    where t.num_rows is null

This gives me 113 rows.

However, I can't figure out how to convert this to a "delete". I was going to just use:

delete from MAPINFO.MAPINFO_MAPCATALOG where tablename in (...)

But this deletes 115 rows. There are two problems:

  1. I need to compare tablename AND ownername. By only comparing tablename I'd be deleting two tables that shouldn't be deleted.
  2. The table has no unique keys and I'm not in a position to create them.

Given the above, how should I go about performing this delete?

Upvotes: 0

Views: 147

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

You can do this:

delete from MAPINFO.MAPINFO_MAPCATALOG where (tablename, ownername) in (...)

Upvotes: 1

Related Questions