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

[Campbell Ritchie]

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:-

CREATE TABLE b
(
number INT AUTO_INCREMENT,
. . .
name VARCHAR(40)
);

And "name" is UNIQUE and "number" is declared as a primary key.

CREATE TABLE b
(
id INT AUTO_INCREMENT,
number INT,
. . .
title VARCHAR(200)
);

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_;

CREATE PROCEDURE add_to_a(IN name_in VARCHAR(40))
INSERT INTO a (name) VALUES
(name_in);

CREATE PROCEDURE insert_into_b(IN number_in INT, title_in VARCHAR(200))
INSERT INTO b (number, title) VALUES
(number_in, title_in);

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:-

DELIMITER |
CREATE PROCEDURE insert_into_b_with_name(IN name_in VARCHAR(40), IN title_in VARCHAR(200))
DEFINE number_in := SELECT number FROM a WHERE name = name_in;
INSERT INTO b (number, title) VALUES
(number_in, title_in); |
DELIMITER ;

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]

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.

DELIMITER |
CREATE PROCEDURE insert_into_b_with_name(IN name_in VARCHAR(40), IN title_in VARCHAR(200))
BEGIN
START TRANSACTION;
@number_in := (SELECT number FROM a WHERE name = name_in);
INSERT INTO b (number, title) VALUES
(@number_in, title_in);
COMMIT;
END; |
DELIMITER ;

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]

You could try putting the declarations immediately after the "begin" statement. Try this, which ought to work

DELIMITER |
CREATE PROCEDURE insert_into_b_with_name(IN name_in VARCHAR(40), IN title_in VARCHAR(200))
DEFINE number_in INT;
BEGIN
START TRANSACTION;
number_in := (SELECT number FROM a tab_a WHERE tab_a.name = name_in);
INSERT INTO b (number, title) VALUES
(number_in, title_in);
COMMIT;
END; |
DELIMITER ;

That should work. Tell me if it doesn't.