MySQL 8 Transaction resulting in error for using multiple sql statements-CodePudding

I have 3 tables, one is named SKU_Data, and 2 are named Fabric_Code and Product_Type respectively. SKU_Data has 2 foreign key columns, one stores id of Fabric_Code and the other stores id of Product_Type.

I wrote an SQL transaction to put data into SKU_Data. (Using MySQL 8)

START TRANSACTION;

SELECT id INTO @fabricId FROM Fabric_Codes WHERE Fabric_Code = 'SOME_CODE';

SELECT id INTO @productTypeId FROM Product_Types WHERE Product_Type = 'SOME_TYPE';

INSERT INTO SKU_Data (Item_Sku_Code, Date_Introduced, Fabric_Id, Product_Type_Id, CP)
VALUES ('SOME_STRING_ID', '2012-04-03 14:00:45', @fabricId, @productTypeId, 41);

IF (ERROR) THEN
  ROLLBACK;
ELSE
  COMMIT;
END IF;

Now I am getting below mentioned error:

SQL Error [1064] [42000]: 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 'SELECT id INTO @fabricId FROM Fabric_Codes WHERE Fabric_Code = 'VELVETS';

SELEC' at line 3

Error position: line: 2

This error too vague to solve, any idea how to go about fixing this?

When I run the query SELECT id INTO @fabricId FROM Fabric_Codes WHERE Fabric_Code = 'VELVETS'; alone it works fine.

I tried changing the delimiter that also didn't work.

CodePudding user response：

Do not use intermediate variables. Execute your action in single INSERT .. SELECT (which is a transaction itself):

INSERT INTO SKU_Data (Item_Sku_Code, Date_Introduced, Fabric_Id, Product_Type_Id, CP)
SELECT 'SOME_STRING_ID', 
       '2012-04-03 14:00:45', 
       Fabric_Codes.id, 
       Product_Types.id, 
       41
FROM Fabric_Codes 
CROSS JOIN Product_Types 
WHERE Fabric_Codes.Fabric_Code = 'SOME_CODE'
  AND Product_Types.Product_Type = 'SOME_TYPE';