Hello all I have a requirements table structured as (req_id, version_id, req_desc, comments).
When a requirement comments is changed by the user, my application increases the version_id for the requirement and saves it as a new record. Sample records: 1,1,'Description 1', 'comments 1'; After updating this shud be 1,2,'Description 1', 'comments 2'; Note that I want to get the description from the previous version(latest available) of the record. I am trying to do this with the following query: Insert into requirement(req_id,version_id, req_desc, comments) select 1,version_id+1,req_desc,'comments 2' from requirement where req_id=1 and version_id = (select max(version_id) from requirement where req_id = 1) I get the following error: ERROR 1110 (42000): Column 'REQ_DESC' specified twice. Can anyone guess what's wrong or suggest an alternate way to do this. ----------- In case you have the patience to create the table and try it out, here are the scripts: Database version: 4.1.1 alpha. Create Table Requirements (req_id integer, version_id integer, req_desc text, comments text); insert into requirements values(1,1,'Description 1', 'comment 1'); Problem Query mentioned above: Insert into requirement(req_id,version_id, req_desc, comments) select 1,version_id+1,req_desc,'comments 2' from requirement where req_id=1 and version_id = (select max(version_id) from requirement where req_id = 1) ----------- [I do not want to use autoincrement fields or anyother MySql feature because the application will have to work with Oracle or MySql :( ] Thanks, Arun N. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
