CODEWITHSUNDEEP
mysqlsql
Amos Wu
Amos Wu

Reputation: 37

Stored procedure for altering data after transferring it from one table to another?

Let's say we have Table A, with one column "destination", and Table B, with a column called "location". I want to create a stored procedure that inserts the data in "destination" from A into "location" from B. Let's say the data in "destination" is an integer--let's make its value "123" for this scenario-- and after it is inserted into Table B, I want the procedure to affix a "4" to the end of it, making the value "1234". How do I go about doing this?

Below is what I have for the stored procedure so far, before adding the edits that need to be made to alter the integer values being transferred:

CREATE PROCEDURE 'stored_procedure' (IN p_destination int)

BEGIN

INSERT INTO table_b(location)

SELECT destination

FROM table_a

WHERE destination = p_destination;

"code here for adding "4" to the end of value"

END

Thanks beforehand!

Upvotes: 0

Views: 36

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this : Assuming that location in table B is also integer column

INSERT INTO table_b(location)
SELECT CAST(CAST(destination AS VARCHAR(30))+'4' AS INT)
FROM table_a
WHERE destination = p_destination;

OR you can right UPDATE command after inserting Data into Table B

UPDATE Table_b
SET destination = CAST(CAST(destination AS VARCHAR(30))+'4' AS INT)

This will Updates all rows from Table_b

Upvotes: 1

Ashu
Ashu

Reputation: 482

Use this insert query in your procedure

insert into table_b(location) select destination+'4' from table_a WHERE destination = p_destination

Upvotes: 0

Related Questions