CODEWITHSUNDEEP
mysql
user8395584
user8395584

Reputation:

Cannot Create this procedure in MySQL

im trying to create a procedure im MySQL Management Studio and I cant find the problem. The procedure is destined to increment the price of Flights from Thailand by 5%.

This is the table of Flights:

create table Flights
(
flightnumber varchar(30) primary key,
departure varchar(30),
destination varchar(30),
flightdate date ,
planemodel varchar(30),
price int,
);

and this is the query im trying the execute:

alter proc setprice
as begin
declare @precent int 
declare @numofrow int
declare @i int
declare @newprice int
declare @n int
select @i=0 
select @precent=5
select @i=0
select @numofrow= (select count(Flights.flightnumber) from Flights )
select @n= 1.0+@precent/100
while ( @i<@numofrow)
begin
select @newprice=(select Flights.price@n from Flights where flights.flightnumber=@i)
update Flights set price=@newprice where destination='Bangkok - Survernabumi'
select @i=@i+1
end
end

Upvotes: 0

Views: 31

Answers (1)

Barmar
Barmar

Reputation: 781068

The problem is that you're updating the price for a different flight than the one you selected. You're also never multiplying the price by @n, and statements like

select @i = 0

should be

SET @i = 0

And you need ; between all the statements. You can't use ALTER PROC to change the code in a procedure, it can only be used to change some of its attributes.

There's no need for a procedure with a loop, it can be done in a single query.

UPDATE flights 
SET price = price * 1.05
WHERE departure = 'Thailand'

Upvotes: 3

Related Questions