Multiple Column Lookups?
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.
Subscribe via RSS