• Post Reply Bookmark Topic Watch Topic
  • New Topic

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

 
Campbell Ritchie
Marshal
Posts: 52519
119
  • Mark post as helpful
  • send pies
  • 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: 52519
    119
    • Mark post as helpful
    • send pies
    • 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: 52519
    119
    • Mark post as helpful
    • send pies
    • 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.
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!