1

It does not recognise the tables that I created above the procedure and it says: unknown table "tourstats" in field list. The table tourstats contains no data intentionally because I insert data with the call of the procedure and when I execute the call of the procedure it says: unknown table "tourstats" in field list and also the same for table "participates".

My goal is to write a procedure that its input is a tour_id. If this tour_id is in the table tourstats in column tourID, I either delete it or update it and if it is not, I insert it. These 3 last actions are done using 3 triggers calling the procedure but that is the easy part.

Here is my code: 

    CREATE DATABASE IF NOT EXISTS TourDB;
        USE TourDB;

        CREATE TABLE if not exists PARTICIPATE (
            CID SMALLINT NOT NULL,
            TOURID VARCHAR(5) NOT NULL,
            PAYMENT DECIMAL(9 , 2 ) NOT NULL,
            PRIMARY KEY (CID , TOURID),
            CHECK (PAYMENT > 0),
            FOREIGN KEY (CID)
                REFERENCES CUSTOMERS (CID)
                ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (TourID)
                REFERENCES TOURS (TourID)
                ON DELETE RESTRICT ON UPDATE CASCADE
        );

        LOAD DATA LOCAL INFILE 'C:\\.........\\participate.csv'
        INTO TABLE participate 
        COLUMNS TERMINATED BY ';'
        LINES TERMINATED BY '\r\n'
        IGNORE 1 LINES;

        CREATE TABLE TourStats (
            tourID VARCHAR(5) NOT NULL,
            numCustomers INT NOT NULL,
            revenue DECIMAL(9 , 2 ) NOT NULL,
            PRIMARY KEY (tourID),
            FOREIGN KEY (tourID)
                REFERENCES Tours (tourID)
                ON DELETE CASCADE ON UPDATE CASCADE
        );



    #PROCEDURE
    delimiter //
    create procedure UpdateTourStats (IN tour_id varchar(10))

    begin

    if (tour_id) in (tourstats.tourID) and (tour_id) = (participate.TOURID) then
    #update
    begin
    update TourStats 
    set tourID = tour_id, numCustomers = count(participate.CID), revenue = sum(participate.PAYMENT);
    end;
    #insert
    elseif (tour_id) not in (tourstats.tourID) and (tour_id) = (participate.TOURID) then
    begin
    insert into TourStats
    values (tourid, count(participate.CID), sum(participate.PAYMENT));
    end;
    #delete
    else
    begin
    delete from tourstats
    where tourstats.tourID = tour_id;
    end;
    end if;

    end//
    delimiter ;

call UpdateTourStats ('tour2');
abhi
  • 1,760
  • 1
  • 24
  • 40
Christodoulos Demetriou
  • 19
  • 4

1 Answers1

0

I just noticed that your first IF clause might be a problem.

if (tour_id) in (tourstats.tourID) and (tour_id) = (participate.TOURID)

You are asking if a value is in a column, or is equal to a column. I think that maybe this should be changed to 

if tour_id in (select tourID from tourstats) 
and tour_id in (select TOURID from participate)

Now you are asking if a value is in a set of data.

Putting a column name directly in the IN clause seems to work for SELECT statements, but when I try using it in a stored procedure, I get the same error you are getting. If I change to the syntax I am suggesting, I don't get an error.

Addendum

Now that you are getting a different error, and I see that you are running the procedure with a value that you hope to get inserted into the table, let's test just that section:

if tour_id not in (select tourID from tourstats) 
and tour_id in (select TOURID from participate) then
begin
insert into TourStats
select TOURID, count(CID), sum(PAYMENT)
from participate where TOURID = tour_id
group by TOURID;
end;
end if;

I think your other statements might need similar tweaking. You should be able to work your way from the inside out, e.g. you should be able to run this successfully:

select TOURID, count(CID), sum(PAYMENT)
from participate where TOURID = 'tour2'
group by TOURID;

And if that works, try this:

insert into TourStats
#Plus the above SELECT statement#

And once you have that working, you are ready to drop that into your SP. Good luck.

CLAbeel
  • 1,078
  • 14
  • 20