• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to extract number from one table and insert it into another table using MySQL

 
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, this is homework . . .

I have got two tables. (I have a few more, but we don't need to discuss them all now.) They are rather like this:-
And "name" is UNIQUE and "number" is declared as a primary key.Here, "id" is a primary key and "number" is a secondary key; it references "number" in table "a". [I mean foreign key, not secondary key.]

Now, I have a couple of stored procedures. Both work nicely, something like this_;I don't need to change "insert_into_a,", but I would like to pass "name" to "insert_into_b", so I would get something like this:-I have tried things like
  • define number int;
  • @number_in := SELECT number FROM a WHERE name = name_in;
  • number_in = a.number WHERE a.name = name_in; etc
  • and none of them works.

    What is the easiest way to get a local number variable to insert?

    CR

    [edit]A minor spelling correction[/edit]
    [ May 07, 2007: Message edited by: Campbell Ritchie ]
     
    Campbell Ritchie
    Marshal
    Posts: 79153
    377
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    I have tried the following sort of thing, with some measure of success[ ], but is it really a good idea to use the @ to get session variables?
    I don't seem to be able to get "declare" [whether I spell it d-e-f-i-n-e or not :p ] to work.Yes, I know you don't need "START TRANSACTION; . . .COMMIT;" when there is only a single statement in the block, but I have other examples where I have multiple statements, and do need transactions. Also a local variable would be preferable to the session variable.

    Anybody got a better suggestion?

    [edit]Change "number_in" to @number_in" at one place.[/edit]
    [ May 08, 2007: Message edited by: Campbell Ritchie ]
     
    Campbell Ritchie
    Marshal
    Posts: 79153
    377
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    You could try putting the declarations immediately after the "begin" statement. Try this, which ought to workThat should work. Tell me if it doesn't.
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic