Having trouble with a procedure in MariaDB

Question

I'm creating a basic airport management system SQL database using MariaDB. I want to create a procedure containing a transaction that makes sure that: Whenever a passenger is moved to another flight, the luggage associated with (belonging to) that passenger, is transferred to that same flight. I keep getting the error code:

Error Code: 1054. Unknown column 'Flight_ID' in 'field list'

Where's the error? Here's my code for the procedure along with the relevant tables from the database:

Code for the procedure

Tables referenced

The procedure:

DELIMITER //
CREATE PROCEDURE PassengerTransfer (
    IN vPassportNo VARCHAR(9), vOldFlightID VARCHAR(4), vNewFlightID VARCHAR(4), OUT vStatus VARCHAR(45))
BEGIN
    START TRANSACTION;
    SET SQL_SAFE_UPDATES = 0;
    UPDATE TRAVELS
    SET Flight_ID = vNewFlightID
    WHERE Flight_ID = vOldFlightID AND Passport_no = vPassportNo;

    UPDATE CARRIES INNER JOIN LUGGAGE ON LUGGAGE.LuggageID = CARRIES.LuggageID
    SET CARRRIES.Flight_ID = vNewFlightID
    WHERE LUGGAGE.Passport_no = vPassportNo;

    IF (TRAVELS.Flight_ID = vNewFlightID AND TRAVELS.Passport_no = vPassportNo)
    AND (CARRIES.Flight_ID = vNewFlightID AND LUGGAGE.LuggageID = CARRIES.LuggageID
    AND LUGGAGE.Passport_no = vPassportNo)
        THEN SET vStatus = "Trasaction PassengerTransfer committed!"; COMMIT;
        ELSE SET vStatus = "Transaction PassengerTransfer rollback"; ROLLBACK;
    END IF;
END; //
DELIMITER ;

The tables referenced:

CREATE TABLE Travels(
Passport_no varchar(9) not null,
Flight_ID varchar(4) not null,
PRIMARY KEY (Passport_no),
FOREIGN KEY (Flight_ID) REFERENCES Flight(Flight_ID) ON UPDATE CASCADE,  
FOREIGN KEY (Passport_no) REFERENCES Passenger(Passport_no) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Luggage(
LuggageID       varchar(5) not null,
Weight          decimal(4,2),                   
Passport_no     varchar(9) not null, 
PRIMARY KEY (LuggageID),
FOREIGN KEY(Passport_no) REFERENCES Passenger(Passport_no) ON UPDATE CASCADE
);

CREATE TABLE Carries(
LuggageID       varchar(5) not null,
Flight_ID   varchar(4) not null,
PRIMARY KEY (LuggageID)
);

Answer 1

The NEW and OLD keywords are available only in triggers, not in procedures. So code like this:

UPDATE TRAVELS SET NEW.Flight_ID = vNewFlightID
WHERE OLD.Flight_ID = vOldFlightID AND Passport_no = vPassportNo;

should be written as:

UPDATE TRAVELS 
SET Flight_ID = vNewFlightID
WHERE Flight_ID = vOldFlightID AND Passport_no = vPassportNo;

Your code:

UPDATE CARRIES SET NEW.Flight_ID = vNewFlightID
WHERE LUGGAGE.LuggageID = CARRIES.LuggageID AND
LUGGAGE.Passport_no = vPassportNo;

should be written as:

UPDATE CARRIES 
   INNER JOIN LUGGAGE ON LUGGAGE.LuggageID=CARRIES.LuggageID
SET CARRIES.Flight_ID = vNewFlightID
WHERE LUGGAGE.Passport_no = vPassportNo;