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
CONNECT BY rownum <= 10000000) a, BOOK b
WHERE a.a1 BETWEEN b.START_NUMBER AND b.START_NUMBER + b.UNITS - 1;
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:
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