Karen:
You nailed it. I omitted the link AND I overcomplicated by over-nesting. Thanks! Bruce From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: Tuesday, October 01, 2013 11:16 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Lack of UPDATE Understanding You don't have the required link between the 2 tables in your update. I'm not sure I have the "where" clause correct because I can't exactly follow what you're trying to do, but I'm sure you get the idea: UPDATE tt_Humpty + SET Unit = t2.Unit + FROM tt_Humpty t1, pkSpace t2 + WHERE t1.Space = t2.Space AND t1.RegNote CONTAINS 'REG EXP:' + and T1.Unit IS NULL Karen -----Original Message----- From: Bruce A. Chitiea <[email protected] <mailto:[email protected]> > To: RBASE-L Mailing List <[email protected] <mailto:[email protected]> > Sent: Tue, Oct 1, 2013 1:09 pm Subject: [RBASE-L] - Lack of UPDATE Understanding All: I'm getting the error: "Syntax is incorrect for the command UPDATE (2045)" Don't understand why. I've PROJECTED out a block of data to temp table TT_HUMPTY so I can't destroy my working table. The data looks like this (Courier New font; names simplified; rows elided): RowID PlateNO Space Unit RegNote ------- ------- ----- ---- ------------ 1166349 6HQD159 H0613 1127 REG EXP: ... 1166340 4SJZ727 H0670 -0- REG EXP: ... 1167795 7V66155 M2214 -0- REG EXP: ... . . . 1166859 6VIK282 H1337 1202 REG EXP: ... ------- ------- ----- ---- ------------ I'm attempting to fill in the missing UNIT values, drawn from table PKSPACE where UNITs relate to SPACEs. The following command produces the correct set of missing UNIT values: SELECT Unit + FROM pkSpace + WHERE Space IN + (SELECT Space FROM tt_humpty + WHERE RowID IN + (SELECT RowID FROM tt_humpty + WHERE RegNote CONTAINS 'REG EXP:' + AND Unit IS NULL)) ... but this UPDATE command: UPDATE tt_Humpty + SET Unit = Unit + FROM pkSpace + WHERE Space IN + (SELECT Space FROM tt_humpty + WHERE RowID IN + (SELECT RowID FROM tt_humpty + WHERE RegNote CONTAINS 'REG EXP:' + AND Unit IS NULL)) ... produces this error message: "Syntax is incorrect for the command UPDATE (2045)" Any perspective greatly appreciated. Bruce Chitiea SafeSectors, Inc. eCondoMetrics 909.238.9012 Cell I
