SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    ********* Ornithologist AtomicPenguin's Avatar
    Join Date
    May 2002
    Location
    Vancouver, BC
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    inserting data into multiple table

    Can you insert into multiple tables with one query? My MySQL book is being surprisingly vague about it.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    pretty sure the answer is no

    like, 99% sure

    how vague is your book?


    rudy
    http://rudy.ca/

  3. #3
    ********* Ornithologist AtomicPenguin's Avatar
    Join Date
    May 2002
    Location
    Vancouver, BC
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd say about 35% vague.

    In which case, what should I do in this case:

    My page has to insert records into two separate tables. Problem is, how can I make it so that it either inserts BOTH or neither?

    I mean, right now I have Insert #1 followed by by #2. #1 may succeed, but then #2 may fail, and my database will get a little out of whack.

    There's no way to link two queries together, maybe?

  4. #4
    ********* Ornithologist AtomicPenguin's Avatar
    Join Date
    May 2002
    Location
    Vancouver, BC
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh wait. I figured it out. What a dummy: just delete the 1st query.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, if you are running the transactions, sure

    i mean, you, sitting at the command line, you're gonna remember if #2 failed so that you can go in and undo the effects of #1

    but setting this up in production takes a little more ingenuity

    there are ways around it...

    create a "whereami" table

    for every transaction,
    insert "i'm about to do #1" into wherami
    get the transaction whereami autonumber
    do #1
    update whereami status to "i'm about to do #2"
    do #2
    update whereami status to "i'm done"

    after a crash, read the whereami table for any unbalanced transactions

    by the way, what we're talking about is two queries in a row, and the mysql folks are very sensitive about not supporting transaction blocks, maybe that's what your book was vague about, and i can understand why, you don't want to focus on a deficiency too much

    originally i thought you mean something like "update table a join table b" and there's for 99% sure no way to do that

    might be nice, though, because then you wouldn't need a transaction block

    rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •