MacLochlainns Weblog

Michael McLaughlin’s Technical Blog

Site Admin

Multiple Column Lookups?

with 2 comments

I’ve been working with Oracle so long, sometimes it’s frustrating when I find a syntax feature isn’t in another database. I ran into another example tonight. There isn’t a multiple column look up operator in MySQL. For example, you can do this in Oracle:

1
2
3
4
5

DELETE FROM common_lookup
WHERE  (common_lookup_table,common_lookup_column) IN
         (('TRANSACTION','PAYMENT_METHOD_TYPE')
         ,('TRANSACTION','TRANSACTION_TYPE')
         ,('RENTAL_ITEM','RENTAL_ITEM_TYPE'));

When I transformed it to comply with MySQL, it seems just as clean. In fact, with strings it’s simpler.

1
2
3

DELETE FROM common_lookup
WHERE  common_lookup_table IN ('TRANSACTION','RENTAL_ITEM')
AND    common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE','RENTAL_ITEM_TYPE');

Then, I thought about it. Oracle would let me write a single subquery returning the two columns, whereas MySQL requires two subqueries in their syntax. Likewise, MySQL doesn’t support the WITH clause, which would let me reference a single query result in the scope of the master query (Oracle and SQL Server do support that).

I guess we can hope that Oracle will implement the feature in MySQL now that they own it. :-) Let me know if I’ve missed some fabulous syntax alternative.

Written by maclochlainn

March 16th, 2010 at 8:00 pm

Posted in MySQL,Oracle,Oracle XE,sql

«
»

2 Responses to 'Multiple Column Lookups?'

Subscribe to comments with RSS or TrackBack to 'Multiple Column Lookups?'.

  1. Those DELETEs are not logically the same. For example, the Oracle version will not match rows like (‘TRANSACTION’,'RENTAL_ITEM_TYPE’), whereas the MySQL one will.

    Jeff Kemp

    16 Mar 10 at 9:26 pm

  2. Absolutely right, but in the event of the data set it works, albeit clumsily. ;-) I wanted to illustrate the differences with the IN operator. The mirrored logic (below) is more typing. I prefer the Oracle syntax.

    1
2
3
4
5
6
7

    DELETE FROM common_lookup
WHERE  common_lookup_table = 'TRANSACTION'
AND    common_lookup_column = 'PAYMENT_METHOD_TYPE'
OR     common_lookup_table = 'TRANSACTION'
AND    common_lookup_column = 'TRANSACTION_TYPE'
OR     common_lookup_table = 'RENTAL_ITEM'
AND    common_lookup_column = 'RENTAL_ITEM_TYPE';

    maclochlainn

    16 Mar 10 at 10:52 pm

Leave a Reply