MySQL update a master field only if condition is true-CodePudding

Is there a way to update SQL in such a way, for example.

Master Table has a column called INVOICESCOUNT.

When an invoice is deleted successfully, then the INVOICESCOUNT is decreased.

For example, a SQL psuedo-code statement like this:

Delete From Invoices where INVOICE=500;
Update Customers SET INVOICECOUNT=INVOICECOUNT-1 WHERE Customer=1  (if prior statement returns 1 affected row);

I need it to be embedded within the same SQL statement instead of having the source code handling executing the 2 statements separately.

Thanks for any advice. Please also let me know if there is any minimal MySQL version requirement if there is such a solution.

UPDATE with more info: note that the list of Customers I present to the user can be very different each time, example, CustomerGroupID=? or CustomerCreated within a certain date, so the Customers query cannot be cached efficiently, as such I prefer to update the INVOICECOUNT (as it will be hit on many times in an hour by different users listing different groups of customers).

CodePudding user response：

Better idea: instead have a VIEW that shows you Customer's invoice counts:

CREATE VIEW CustomersInfo AS

SELECT
    CustomerId,
    COUNT(*) AS InvoiceCount
FROM
    Invoices
GROUP BY
    CustomerId
;

Then you'd use it like so:

SELECT
    c.CustomerId,
    COALESCE( ci.InvoiceCount, 0 ) AS InvoiceCount
FROM
    Customers AS c
    LEFT OUTER JOIN CustomersInfo AS ci ON c.CustomerId = ci.CustomerId

(Don't use an INNER JOIN, otherwise Customers without any invoices won't be in the output).

CodePudding user response：

You can use triggers for that

CREATE TABLE Invoices(INVOICE INT)

CREATE TABLe Customers(Customer int,INVOICECOUNT int)

INSERT INTO Customers VALUES (1,1)

CREATE TRIGGER del_after AFTER DELETE ON Invoices
FOR EACH ROW
Update Customers SET INVOICECOUNT=INVOICECOUNT-1 WHERE Customer=1

Delete From Invoices where INVOICE=500;

SELECT * FROM Customers

Customer | INVOICECOUNT
-------: | -----------:
       1 |            1

INSERT INTO Invoices VALUES (400)

Delete From Invoices where INVOICE=500;

SELECT * FROM Customers

Customer | INVOICECOUNT
-------: | -----------:
       1 |            1

INSERT INTO Invoices VALUES (500)

Delete From Invoices where INVOICE=500;

SELECT * FROM Customers

Customer | INVOICECOUNT
-------: | -----------:
       1 |            0

db<>fiddle here

CodePudding user response：

As was mentioned in the comments, you probably don't need to store the invoice count in a table column, however if you MUST have the invoice count column for any reason your best bet might be a stored procedure.

DELIMITER //
CREATE PROCEDURE removeinvoice
BEGIN
    DELETE FROM invoices WHERE invoice=500;
    UPDATE customers SET invoicecount = (SELECT COUNT(*) FROM invoices);
END//
DELIMITER ;

Then you just call that stored procedure.

CALL removeinvoice;