Converting access SQL to MYSQL, How to If(isnull(Max( a column on Insert

Question

beginner here. I'm converting Access sql to MYSQL so I can run bash files and I ran into this 1 issue where 3 days of web searching as lead me no where.

I have a table with two primary fields, "SalesOrderId" and "SOItemID" So the table may look like this:

+--------------+----------+--------+
| SalesOrderid | SOItemId | PartId |
+--------------+----------+--------+
|   10001      |     1    |  147   |
|   10002      |     1    |  152   |
|   10003      |     1    |  152   |
|   10003      |     2    |  188   |
|   10004      |     1    |  105   |
|   10004      |     2    |  84    |
|   10004      |     3    |  209   |
|   10005      |     1    |   5    |
+--------------+----------+--------+

On insert, i need to check if the SalesOrderId exists and if so, +1 the SOItemId field and insert the new record. If not then insert SOItemId as 1. So if I were to insert another PartId to SalesOrderId# 10004, it'd insert as (10004, 4, 299) Here is the code in Access SQL that currently works.

SQL Code:

INSERT INTO SOItem ( SalesOrderId, SOItemId, PartId, CustDeliv, OnDate, Qty, UnitAmount, WebOrderProductId )
SELECT SalesOrder.SalesOrderId, If(IsNull(Max(`SOItemId`,"SOItem","SalesOrderId= " & [SalesOrderId] & " ")),1,DMax("[SOItemId]","SOItem","SalesOrderId= " & [SalesOrderId] & " ")+1) AS Expr1, ICS_Web_Parts_Link.PartId, SalesOrder.Date, SalesOrder.Date, order_product.quantity, order_product.price, order_product.order_product_id
FROM (order_product INNER JOIN ICS_Web_Parts_Link ON order_product.model = ICS_Web_Parts_Link.Model) INNER JOIN SalesOrder ON order_product.order_id = SalesOrder.WebOrderId;

Here's my MYSQL version:

INSERT INTO `SOItem` ( SalesOrderId, SOItemId, PartId, CustDeliv, OnDate, Qty, UnitAmount, WebOrderProductId )
SELECT `SalesOrder`.`SalesOrderId`, If(IsNull(Max(`SOItem`.`SOItemId`, `SOItemId`.`SalesOrderId` = `SalesOrder`.`SalesOrderId`)),1,Max(`SOItem`.`SOItemId`, `SOItemId`.`SalesOrderId` = SalesOrder`.`SalesOrderId`)+1) AS Expr1, ICS_Web_Parts_Link.PartId, SalesOrder.Date, SalesOrder.Date, order_product.quantity, order_product.price, order_product.order_product_id
FROM (order_product INNER JOIN ICS_Web_Parts_Link ON order_product.model = ICS_Web_Parts_Link.Model) INNER JOIN SalesOrder ON order_product.order_id = SalesOrder.WebOrderId;

I get this error:

#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 ' SOItemId.SalesOrderId = SalesOrder.SalesOrderId)),1,Max(SOItem.`SOIte' at line 2

Any help would be greatly appreciated. -Dan

Answer 1

The syntax SOItemId.SalesOrderId implies that you're referencing a table called SOItemId, which has a column called SalesOrderId. That's clearly not what you're doing (there's no table with that name, at least not joined to this query), so that's why it's throwing a syntax error at you.

It's not clear exactly what you are trying to join to what. I suggest you spend some more time looking at SQL exercises and examples of working code. While you're at it, make sure you understand how MAX and ISNULL work in MySQL. (Consider using IFNULL or COALESCE instead.)

Answer 2

Here's how to do this in MySQL. This is just an example using what you referenced above but you can execute it as-is to see how it works and then apply it to your use-case.

DROP TABLE IF EXISTS sales_test;

CREATE TABLE `sales_test` (
  `SalesOrderid` int(11) DEFAULT NULL,
  `SOItemId` int(11) DEFAULT NULL,
  `PartId` int(11) DEFAULT NULL,
PRIMARY KEY (SalesOrderid, SOItemId),
  UNIQUE KEY `SalesOrderid` (`SalesOrderid`,`SOItemId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO sales_test (SalesOrderid, SOItemId, PartId) VALUES 
(10001, 1, 147),
(10002, 1, 152),
(10003, 1, 152),
(10003, 2, 188),
(10004, 1, 105),
(10004, 2, 84),
(10004, 3, 209),
(10005, 1, 5);

SELECT * FROM sales_test;

INSERT INTO sales_test (SalesOrderid, SOItemId, PartId) 
VALUES (10005, 1, 5)
ON DUPLICATE KEY UPDATE SOItemId = SOItemId + 1;

SELECT * FROM sales_test;

Answer 3

I was finally able to figure it out. Thanks for all your help! To anybody with the same problem, here is the mysql code that works.

INSERT INTO `SOItem` ( SalesOrderId, SOItemId, PartId, CustDeliv, OnDate, Qty, UnitAmount, WebOrderProductId )
SELECT `SalesOrder`.`SalesOrderId`, IF((SELECT (SOItem.SOItemId) FROM d1.SOItem WHERE SalesOrder.SalesOrderId = SOItem.SalesOrderId) IS NULL, 1, (SELECT MAX(SOItem.SOItemId)+1 FROM d1.SOItem WHERE SalesOrder.SalesOrderId = SOItem.SalesOrderId)) AS Expr1, ICS_Web_Parts_Link.PartId, SalesOrder.Date, SalesOrder.Date, order_product.quantity, order_product.price, order_product.order_product_id
FROM d2.order_product INNER JOIN d1.ICS_Web_Parts_Link ON order_product.model = ICS_Web_Parts_Link.Model INNER JOIN d1.SalesOrder ON order_product.order_id = SalesOrder.WebOrderId LEFT JOIN d1.SOItem ON order_product.order_product_id = SOItem.WebOrderProductId
WHERE NOT EXISTS (SELECT order_product_id FROM d2.order_product WHERE order_product.order_product_id = SOItem.WebOrderProductId);