Question 290200 in Mysql Question posted in
The official MySQL documentation can be found here.

Cannot set a variable from stored function result in MySQL Update Statement

I am trying to use a variable to hold the results of a stored function in MySQL. I get a syntax error that I cannot reconcile when I try to set the variable.

The name of the function is checkAddress(v1, v2, v3, v4) and returns a string. I want to put the results into a variable and then update two fields with parts of the returned string.

I am looking for the correct syntax to create and use a variable in an UPDATE statement.

UPDATE t1 n
JOIN t2 t ON n.id = t.id
SET @temp:=checkAddress(t.v1, t.v2, t.v3, t.v4),
n.address1 = LEFT(@temp, 10),
n.address2 = RIGHT(@temp, 20);

I get a syntax error at ‘@’.

I tried a test of the following:

SELECT @temp:=checkAddress(ADDRESS_1,ADDRESS_2,ADDRESS_3,ADDRESS_4) AS 'holder',
CONCAT("X",' ',@temp) FROM table;

This results in a column ‘holder’ with an ‘X’ prepended to the returned string from the function. This was to prove that variables can be set in the system and the function works.

Note: The LEFT and RIGHT functions are for illustration purposes to show that I will use the @temp variable to set other fields. It dose not matter what is after the SET @temp:= as I cannot get past this line.

Tags:

2

Answers


  1. 0

    You cannot directly set user variables within an UPDATE statement. However, you can achieve your goal using a subquery to assign the result of the function to a variable and then use that variable in the UPDATE statement.

    For example:

    SET @temp := (SELECT checkAddress(t.v1, t.v2, t.v3, t.v4) FROM t2 t WHERE t.id = n.id);

UPDATE t1 n
JOIN t2 t ON n.id = t.id
SET n.address1 = LEFT(@temp, 10),
    n.address2 = RIGHT(@temp, 20);

    The result of the checkAddress function for each corresponding record is stored in @temp variable through the subquery, and then you can use @temp in the subsequent UPDATE statement to set the values of address1 and address2.

    Important to note that this query assumes that you’re updating t1 based on the JOIN condition with t2 and it’s crucial that each record in t1 has a corresponding match in t2 based on the id column. Adjust the JOIN condition accordingly based on your schema.

    Login or Signup to reply.
  2. 0 
    UPDATE t1 n
JOIN t2 t ON n.id = t.id
JOIN LATERAL (SELECT checkAddress(t.v1, t.v2, t.v3, t.v4) tmp) tmp
SET n.address1 = LEFT(tmp.tmp, 10), n.address2 = RIGHT(tmp.tmp, 20);

    https://sqlize.online/sql/mysql80/92ec633fe9d91409f5d890724199b9c8/
    https://www.db-fiddle.com/f/x5BRNGAWzf6QXBoKvB2PEw/0

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search