Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • 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
Sheriff
Posts: 48921
58
  • 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
    Sheriff
    Posts: 48921
    58
    • 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
    Sheriff
    Posts: 48921
    58
    • 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.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic