MYSQL Creating new Stored Procedure

Question

I am trying to create below stored procedure in MY SQL:

create procedure UpdateLineItemGAPSTATOTH(IN TIP_LI_ID INT)
BEGIN
Update tip_entry_line_item set tip_entry_line_item.ACCT_NAME = app_gl_bridge.gl_description 
from tip_entry_line_item LEFT JOIN 
select app_gl_bridge.gl_code,app_gl_bridge.gl_description 
  FROM app_gl_bridge GROUP BY gl_code,gl_description ON tip_entry_line_item.GL_ACCT_CODE = app_gl_bridge.gl_code 
  WHERE tip_entry_line_item.TIP_LI_ID = TIP_LI_ID;
END

however, it is throwing error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from tip_entry_line_item LEFT JOIN select app_gl_bridge.gl_code,app_gl_bridge.' at line 4

score 0 · Answer 1 · edited May 23 '17 at 10:27

You SQL syntax looks a bit messy to me. There is no FROM in the UPDATE syntax, and table references should be placed before the SET part.

 UPDATE [LOW_PRIORITY] [IGNORE] table_reference
 SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
 [WHERE where_condition]
 [ORDER BY ...]
 [LIMIT row_count]

MySQL doc

Same for the SELECT subquery (GROUP BY should be at the end), and also you have to put it between brackets

I'm not sure what your query should do, you can take a look at this answer regarding UPDATE and subquery : MySQL - UPDATE query based on SELECT Query

MYSQL Creating new Stored Procedure

1 Answers1