Thanks for this Dennis. I’ve always wondered how R:Base knows which table is which when you’re trying to update values in a table from another row in the same table. e.g:
UPDATE Exam SET Result=(LMAX(t2.Result,t1.Result)) + FROM Exam t1,Exam t2 + WHERE t2.PersonID=t1.PersonID + AND t1.SittingDate=.vDate1 AND t2.SittingDate=.vDate2 Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Wednesday, June 27, 2012 5:08 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: 'Undefined Table' error Bruce, It would be better to understand what is happening. You are using two versions of the same table. The first has no alias, the second does. You can’t say UPDATE pksimport SET t1.pksid = whatever because the column t1.pksid is not in that copy of the table. This syntax gives you a better idea what is happening: UPDATE pksimport T0 SET t0.pksid = t2.pksid + FROM pksimport t1, pksurveytest t2 + WHERE t2.pksdate = t1.pksdate AND + t2.pkstime = t1.pkstime Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected]
