File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to extract number from one table and insert it into another table using MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to extract number from one table and insert it into another table using MySQL" Watch "How to extract number from one table and insert it into another table using MySQL" New topic
Author

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

Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38340
    
  23
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

    Joined: Oct 13, 2005
    Posts: 38340
        
      23
    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

    Joined: Oct 13, 2005
    Posts: 38340
        
      23
    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.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: How to extract number from one table and insert it into another table using MySQL