CODEWITHSUNDEEP
mysqlxampplampp
Wachaga Mwaura
Wachaga Mwaura

Reputation: 3600

The error occurs on another setup, not on mine: "Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in MySQL"

My question is linked to the one below:

Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql

In my case, the trigger below works fine on my local setup (LAMPP on Ubuntu):

-- trigger to increment balance on posting of a new bill
DELIMITER
    //
CREATE TRIGGER trig_increment_balance_new_bill AFTER INSERT ON
    tbl_bill FOR EACH ROW
BEGIN
    DECLARE
        vBillAmount DOUBLE ;
    SELECT NEW
        .amount
    INTO vBillAmount ;
UPDATE
    tbl_client T
SET
    balance = balance + vBillAmount
WHERE
    id =(
    SELECT
        id
    FROM
        tbl_client C
    WHERE NEW
        .metreNo = C.metreNo
    LIMIT 1
) ;
END ; //
DELIMITER
    ;

However, the error below occurs on my collaborator's setup (XAMPP on Windows) when they recreate the same code and test it.

SQLSTATE[HY000]: General error: 1093 Table 'T' is specified twice, both as a target for 'UPDATE' and as a separate source for data

I thought it was related to strict mode, but I found strict mode is activated; 'STRICT_ALL_TABLES' in my case. Why would errors occur on his setup but not on mine? I know the solution provided in the link above may cure this error but the question is why the error is occurring selectively?

Upvotes: 0

Views: 64

Answers (1)

Barmar
Barmar

Reputation: 781058

There's no need for the subquery, just put the criteria directly in the WHERE clause. You also don't need the vBillAmount variable.

CREATE TRIGGER trig_increment_balance_new_bill AFTER INSERT ON tbl_bill 
FOR EACH ROW
    UPDATE tbl_client c 
    SET c.balance = c.balance + NEW.amount 
    WHERE c.metreNo = NEW.metreNo
    LIMT 1;

Upvotes: 2

Related Questions