I wanted to install Oracle, something I often hate to do. I decided to install Oracle's slim personal database, Oracle XE. I only needed something for running a simple query (that required Oracle's SQL syntax, more on that later) and maybe a little development.
Oracle has a browser based client, which is actually pretty cool. Only problem is that it must install a web server for this and on my system it installed this on port 8080. I normally run Tomcat on port 8080. I tried to figure out how to change this inside Oracle. No luck. However, I found a great blog post that mentioned how to solve this.
Now on to what I wanted to do with Oracle. A co-worker had mentioned a SQL brain-teaser. Let's say you have a table, let's call it users, with exactly one column name. This column is not only not a primary key for the table, it is not unique. So you could have multiple instances of "John". Let's say there are three "John"s. You want to delete all of them but one, and you want to do this for any value of name that occurs more than once. The key is to do it with one SQL statement.
The obvious answer to me was to copy all the data into a temp table, but copy it uniquely. Then truncate the frist table, and copy the data back into it. That's three SQL statements though, and that's using an insert select statement as part of the create table statement.
Another way was to add a column to users and put in some sequence of numbers in that column. Then you can use that column as part of your where clause. I realized that Oracle does that auotmatically for you with its meta-column rowid. So that's why I wanted to install Oracle and try it out. Here's the SQL statement:
delete from users a where exists (select b.name from users b where a.name=b.name and b.rowid > a.rowid);
No comments:
Post a Comment