Elie Elie - 11 days ago 5
MySQL Question

Oracle to MySQL syntax

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

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
Comments