Thursday, October 13, 2016

Oracl sql notes

I'm about as far from a Sql expert as its possible to get, so these notes are probably qiute basic

  • Insert into a table only if row does not exists
SELECT 'jonny', NULL
  FROM dual -- Not Oracle? No need for dual, drop that line
 WHERE NOT EXISTS (SELECT NULL -- canonical way, but you can select
                               -- anything as EXISTS only checks existence
                     FROM table
                    WHERE name = 'jonny'

Note its possible to have multiple conditions, e.g.g if table3 also has expected data
    SELECT HIBERNATE_SEQUENCE.NEXTVAL, 1, (select id from table2 where account='052BAFJJ8'), (select id from table3 where name='ABCDE'), 1, 0 FROM dual
     WHERE NOT EXISTS (SELECT id FROM table WHERE account_id = (select id from table2 where account='052BAFJJ8'))
  and EXISTS (SELECT id FROM table3 where name='ABCDE');
Note, there may be race conditions with this approach. In our case we were running it in liquibase scripts, and we didn't have multiple servers running in parallel so this wasn't an issue
  • Delete duplicates
After all my inserts, I ended up with some unexpected duplicates. so I had to delete them. 
I was able to find te duplicate rows easily enough. I searched for all rows with a the same name having a count >1 to find duplicates.
However to delete them we had foreign key relationships, that meant that we could only delte the newly created rows. But these weren't easily identifiable. We decided on the following approach.
We ran the following expression twice. Once with max(rowid) and once with min(rowid), since the table had foreign key dependencies it couldn't be reliably be delted, so this way I managed to get all the duplicates. note if you have many duplicates this may prove more problematic.
exec dbms_errlog.create_error_log(dml_table_name => 'table3' ,err_log_table_name => 'table3_ERRORS') DELETE FROM table3 where rowid in (select min(rowid) from table3 group by name having count(*)>1) log errors into dcu_fund_ERRORS('Is referenced') reject limit 999999999;

No comments: