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]>
To: RBASE-L Mailing List <[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
