You are here:

MySQL/mysql error in procedure-unknown column in field list

Advertisement


Question
QUESTION: I am trying to fetch records from plan_data table in a cursor and trying to update that table depending on some conditions but it is showing error as unknown column in field list.
My code is :

CREATE DEFINER=`root`@`localhost` PROCEDURE `updtpaymt`(pdt date,pamt int(10),unm varchar(25),totalbal int(10))




begin

DECLARE res varchar(900) default "";
DECLARE _prdt varchar(900) default "";
DECLARE _bal varchar(900) default "";
DECLARE _ramt varchar(900) default "";
DECLARE finished INTEGER DEFAULT 0;
DECLARE tp int default 0;

declare updt cursor for select payment_rcv_dt,balance,pay_amt from plan_data where balance>0 and username=unm;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;

open updt;


myloop : loop
  fetch updt into _prdt,_bal,_ramt;

  if pamt > _bal then
set pamt = pamt - _bal;
update plan_data set _prdt=pdt,_bal=0,_ramt=pamt;
  end if;

  if pamt < _bal then

set tp = _bal - pamt;
update plan_data set _prdt=pdt,_bal=temp,_ramt=pamt;
set pamt = pamt - _bal;
  end if;

  IF v_finished = 1 THEN
      LEAVE myloop;
  end if;
  if pamt<=0 then
     leave myloop;
  end if;

end loop;
close updt;

end;

ANSWER: Dear sir

Unknown column is a common mysql error. Which basically means the column  (or table feild) that you have declared in you code is not found in the mysql database table.

In the mysql database table your feild names are columns so there is a spelling mmismatch between the database table and your code.



---------- FOLLOW-UP ----------

QUESTION: Sir all columns i specified here match plan_data table.It is showing me error as:
unknown column _prdt in field list,
i.e in the fetch records line

Answer
Dear sir

If that's showing as an error unknow column then the select qry cannot see that table. Have you confirmed that the column is present?

Remove that column from the qry and run the qry again. If the error no longer shows then it is that column that is not there.  

MySQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


stuart gregory

Expertise

PHP html, xhtml, css coding and MySQL

Experience

My experiance is all first had as I am web developer and I have, and do build many websites all using php and mysql. This ranges from building a simple search engine to secure php login scripts. Some websites like http://www.web-application-development.info are complete database driven websites.

Organizations
I have a large number of websites which I host and maintain, such as. http://www.webreger.com http://www.universalscience.co.uk http://www.modellingelectronics.co.uk

Education/Credentials
Web application development. (Open University) BSC Honers Computing and development (Open University)

©2016 About.com. All rights reserved.