MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Multiple Column Lookups?

with 4 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

4 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

  3. Works for me:

    mysql> SELECT * FROM t1;
    +——+——+
    | a | b |
    +——+——+
    | 1 | 1 |
    | 1 | 2 |
    | 2 | 3 |
    | 2 | 4 |
    +——+——+
    4 rows in set (0.03 sec)

    mysql> SELECT * FROM t1 WHERE (a, b) IN ((1, 1), (2, 3));
    +——+——+
    | a | b |
    +——+——+
    | 1 | 1 |
    | 2 | 3 |
    +——+——+
    2 rows in set (0.06 sec)

    mysql> DELETE FROM t1 WHERE (a, b) IN ((1, 1), (2, 3));
    Query OK, 2 rows affected (0.02 sec)

    mysql> SELECT * FROM t1;
    +——+——+
    | a | b |
    +——+——+
    | 1 | 2 |
    | 2 | 4 |
    +——+——+
    2 rows in set (0.00 sec)

    Scott

    6 Jun 11 at 2:37 pm

  4. Great, don’t recall which version that I tested, but MySQL 5.5 works!!!!

    maclochlainn

    6 Jun 11 at 4:31 pm

Leave a Reply