MySQL UPDATE语句中通过存储函数结果设置变量的语法错误问题

The problem with your original UPDATE statement is that MySQL doesn't allow assigning user variables (@temp) directly as a separate entry in the SET clause of an UPDATE—this clause is meant for assigning values to columns of the tables being updated, not standalone variables.

Here's a clean, efficient way to achieve what you want by calculating the checkAddress result once per row using a derived table, then using that result to update your columns:

UPDATE t1 n
JOIN (
    SELECT 
        id,
        checkAddress(v1, v2, v3, v4) AS addr_result
    FROM t2
) t ON n.id = t.id
SET 
    n.address1 = LEFT(t.addr_result, 10),
    n.address2 = RIGHT(t.addr_result, 20);

Why this works: #

• The derived table (t) computes the checkAddress output once for each row in t2, storing it in addr_result.
• We join this derived table to t1 on the id column, then use the precomputed addr_result to update both address1 and address2 without needing a user variable.
• This avoids any variable-related syntax issues and ensures the function is only called once per row, which is more efficient than calling it twice (once for each column).

If you still want to use a user variable (not recommended): #

While the derived table approach is better practice, you can work around the syntax issue by assigning the variable in a JOIN clause instead of the SET clause. However, this is less readable and can lead to unexpected behavior if not handled carefully:

UPDATE t1 n
JOIN t2 t ON n.id = t.id
JOIN (SELECT @temp := '') AS init -- Initialize the variable
SET 
    n.address1 = LEFT(@temp := checkAddress(t.v1, t.v2, t.v3, t.v4), 10),
    n.address2 = RIGHT(@temp, 20);

In this case, the variable is assigned as part of the address1 calculation, and then reused for address2. But the derived table method remains the preferred approach for clarity and reliability.

内容的提问来源于stack exchange，提问作者Landrew