Oracle to MySQL syntax question

Question

Can someone show me the MySQL equivalent of the following statement (which works in Oracle 10g)?

INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION) 
SELECT a.a1, b.ID, b.DENOMINATION FROM
(SELECT rownum a1 FROM dual CONNECT BY rownum <= 10000000) a, BOOK b
where a.a1 between b.START_NUMBER and b.START_NUMBER+b.UNITS-1;

Basically, what this is doing is for every entry in the table BOOK, it is making entries in the table VOUCHER. The number of entries in the VOUCHER table is determined by the number of UNITS in the corresponding row in the BOOK table. I can provide more information if this is unclear.

Answer 1

The select from DUAL is effectively giving you a large temporary table to join with BOOK. You are allowing for BOOK.UNITS to have values up to 10,000,000.

If units is typically a lot lower than this one option would be to create a dummy table with that number of records in it and join to that instead of the DUAL construct.

Answer 2

The main weirdness going on here is the SELECT ROWNUM FROM DUAL CONNECT BY... -- a kludge that's giving you incrementing numbers starting at the value of BOOKS.START_NUMBER; those then seem to be used as primary keys in the VOUCHER table.

Really, this is a symptom of an awful underlying data model: keeping track of the "start number" and "units" in a table -- it's like a C array gone bad in a database.

If you're taking the time to port this from Oracle to MySQL, seriously give some thought to fixing the data model. Perhaps you want to:

• add a book_id column to the VOUCHER table and subsequent foreign key to the BOOK table
• make the VOUCHER_NUMBER column on the new VOUCHER table in MySQL to be an auto-increment type so you can eliminate the whole start number / units mess

With those in place, use whatever language you're using to implement business logic in MySQL and implement this pseudo-code:

for 1 to BOOK.units loop

  INSERT INTO VOUCHER (
      -- VOUCHER_NUMBER handled by auto-increment
      BOOK_ID
    , DENOMINATION
  )
  SELECT 
      b.ID
    , b.DENOMINATION
  FROM BOOK b
  WHERE b.ID = [book ID];

end loop