CREATE TRIGGER insert_order_trigger AFTER INSERT ON order_info EACH ROW BEGIN UPDATE order_info SET order_info.ord_price = ( SELECT food_info.food_price FROM food_info WHERE food_info.food_name = NEW.food_name ) WHERE food_info.food_no = NEW.food_no; END > 1064 - 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 'EACH ROW BEGIN UPDATE order_info SET order_info.ord_price = ( SELECT food_i' at line 1

根据您提供的 SQL 语句和错误提示，发现有几个问题需要修正：

1. 在触发器名称 "insert_order_trigger" 后面缺少了一个空格。

2. 触发器中的表名应该是 "order_info" 而不是 "order_table"。

3. 在 SET 子句中，应该使用 NEW.food_name 而不是 NEW.dish_name，因为您在订单表中使用的是 "food_name" 字段。

4. WHERE 子句应该是 "order_info.food_no = NEW.food_no" 而不是 "food_info.food_no = NEW.food_no"。

根据上述修正，以下是修正后的 SQL 语句：

CREATE TRIGGER insert_order_trigger AFTER INSERT ON order_info
FOR EACH ROW
BEGIN
    UPDATE order_info
    SET order_info.ord_price = (SELECT food_info.food_price FROM food_info WHERE food_info.food_name = NEW.food_name)
    WHERE order_info.food_no = NEW.food_no;
END;

希望这个修正能够解决您的问题！

相关问题

select * from ( select row_.*, rownum rownum_ from ( select * from ( select distinct OB.BUSI_ORDER_ID, 0 as HIS_ID, OB.BUSI_CODE, OB.CUST_ID, OB.CEASE_REASON, OB.ORDER_STATE, OB.CHANNEL_TYPE, ob.user_id, OB.IS_BATCH_ORDER, OB.APPLICATION_ID, OB.CREATE_DATE, OB.DONE_DATE, OB.EFF_DATE, OB.EXP_DATE, OB.OPER_ID, OB.ORG_ID, OB.REGION_ID, OB.NOTE, OB.PROCESS_STATE, nvl(oi.cust_name, ic.cust_name) cust_name, nvl(oc.icc_id, iu.icc_id) icc_id, nvl(oc.svc_num, iu.svc_num) svc_num, icp.cust_name parent_cust_name, icp.cust_id parent_cust_id, ol.order_list_id from ord_busi ob left join ord_offer oo on oo.busi_order_id = ob.busi_order_id and ob.user_id = oo.user_id left join info_user iu on oo.user_id = iu.user_id left join info_cust ic on ob.cust_id = ic.cust_id left join ord_cust oi on ob.cust_id = oi.cust_id and ob.busi_order_id = oi.busi_order_id left join info_cust icp on nvl(ic.parent_cust_id, oi.parent_cust_id) = icp.cust_id left join ( SELECT * FROM ord_user WHERE user_order_id IN ( SELECT MAX(user_order_id) user_order_id FROM ord_user GROUP BY busi_order_id,user_id ) ) oc on ob.user_id = oc.user_id and ob.busi_order_id = oc.busi_order_id left join ord_list ol on ob.busi_order_id = ol.busi_order_id WHERE 1 = 1 and OB.CUST_ID IN( SELECT DISTINCT CUST_ID FROM (SELECT CUST_ID, PARENT_CUST_ID FROM INFO_CUST UNION SELECT CUST_ID, PARENT_CUST_ID FROM ORD_CUST) TMP START WITH TMP.CUST_ID = '10001009208' CONNECT BY TMP.PARENT_CUST_ID = PRIOR TMP.CUST_ID ) union all select distinct OB.BUSI_ORDER_ID,OB.HIS_ID, OB.BUSI_CODE, OB.CUST_ID, OB.CEASE_REASON, OB.ORDER_STATE, OB.CHANNEL_TYPE, OB.USER_ID, OB.IS_BATCH_ORDER, OB.APPLICATION_ID, OB.CREATE_DATE, OB.DONE_DATE, OB.EFF_DATE, OB.EXP_DATE, OB.OPER_ID, OB.ORG_ID, OB.REGION_ID, OB.NOTE, OB.PROCESS_STATE, nvl(oi.cust_name,ic.cust_name) cust_name , nvl(oc.icc_id, iu.icc_id) icc_id, nvl(oc.svc_num,iu.svc_num) svc_num, icp.cust_name parent_cust_name, icp.cust_id parent_cust_id, ol.order_list_id from ord_busi_his ob left join ord_offer_his oo on oo.busi_order_id = ob.busi_order_id 优化一下

可以考虑以下优化方式：

1. 使用INNER JOIN代替LEFT JOIN：如果确保两个表中都含有匹配的行，则使用INNER JOIN可以提高查询效率。

2. 使用EXISTS代替IN：EXISTS执行速度比IN更快，因为它只需要找到匹配的第一个结果，而不是找到所有匹配的结果。

3. 简化子查询：在查询中可以尝试简化子查询，以减少查询的复杂度。

4. 使用索引：使用索引可以提高查询效率，可以根据查询中经常使用的列添加索引。

5. 避免使用SELECT *：只查询需要的列，避免使用SELECT *可以减少查询的复杂度，提高查询效率。

SELECT pr.PRODUCT_ID, pr.PRODUCT_NAME, pr.ORIGINAL_PRICE, pr.ONLINE_PRICE, pr.COMMISSION_PERCENT, pr.TOTAL_SALES, pr.SHOP, dict.label AS COMMERCE_TYPE_LABEL, pr.SERVICE_RATE, ord.ORDER_ID, date.label AS SEND_FLG_LABEL, pro.CONNECT_BUSINESS_FLG, address.APPLY_REMARK, address.CREATE_TIME, users.nicknameFROM t_product AS prLEFT JOIN t_order AS ord ON ord.PRODUCT_ID = pr.IDLEFT JOIN ( SELECT label, `value` FROM system_dict_data WHERE dict_type = 'COMMERCE_TYPE' ) AS dict ON dict.`value` = pr.COMMERCE_TYPE LEFT JOIN ( SELECT label, `value` FROM system_dict_data WHERE dict_type = 'trade_after_sample_type' ) AS date ON date.`value` = ord.SEND_FLGLEFT JOIN t_apply_pro AS pro ON pro.PRODUCT_ID = pr.IDLEFT JOIN t_apply_pro_user_address AS address ON address.t_apply_pro_id = pro.IDLEFT JOIN system_users AS users ON users.id = pro.OPERATE_USER_ID WHERE users.id = '1'

这是一个SQL语句，查询了多张表的数据并进行了联合查询，最终筛选出符合条件的数据。具体查询条件为：查询商品表t_product中的PRODUCT_ID、PRODUCT_NAME、ORIGINAL_PRICE、ONLINE_PRICE、COMMISSION_PERCENT、TOTAL_SALES、SHOP、COMMERCE_TYPE_LABEL、SERVICE_RATE字段，同时关联订单表t_order，查询ORDER_ID、SEND_FLG_LABEL字段，关联申请表t_apply_pro，查询CONNECT_BUSINESS_FLG字段，关联申请地址表t_apply_pro_user_address，查询APPLY_REMARK、CREATE_TIME字段，同时关联用户表system_users，查询nickname字段。其中，使用LEFT JOIN进行表的关联，使用WHERE进行条件筛选，筛选条件为users.id = '1'。