CODEWITHSUNDEEP
mysqlsqlselectprocedure
Ishini Kiridena
Ishini Kiridena

Reputation: 25

MySQL procedure not working with both select and delete

I created a procedure to get a vehicle id from the vehicle table using vehicle number input, then delete the booking record for that vehicle id, and at last to delete the entry in the vehicle table... But when creating the procedure I get an error at line 7 saying Error Code: 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 '' at line 7 Please help me to identify the issue... Thanks in advance

CREATE PROCEDURE `DeleteVehicleWithBookings`(IN vehicleNo VARCHAR(10))
BEGIN

    #get vehicle id
    select @vid = vehicleID
    from vehicle
    where vehicle_no = vehicleNo;

    #delete booking
    delete from booking where vehicleID = vid;
    
    #delete vehicle
    delete from vehicle where vehicle_no = vehicleNo;
END 
DELIMITER ;

Upvotes: 1

Views: 141

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

First, I would recommend prefixing your parameters so they are more clearly distinguished from column names.

Then, you are not assigning and using the variables correctly. So, your intention is more like this:

CREATE PROCEDURE `DeleteVehicleWithBookings`(
    IN in_vehicleNo VARCHAR(10)
)
BEGIN
    #get vehicle id
    select @vid := vehicleID
    from vehicle
    where vehicle_no = in_vehicleNo;

    #delete booking
    delete from booking where vehicleID = @vid;
    
    #delete vehicle
    delete from vehicle where vehicle_no = in_vehicleNo;
END 
DELIMITER ;

The first two statements can be written without a variable, though:

#delete booking
delete b from booking b
    where b.vehicleID = (select v.vehicleID
                         from vehicle v
                         where v.vehicle_no = in_vehicleNo
                        );

And, you could probably define a cascading delete foreign key constraint and eliminate the stored procedure entirely.

Upvotes: 1

Related Questions