If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

update from record in same table

Hi all,

I have this table with records on services we give to customers. If changes occur in these services, the records aren't updated, but "closed" and a new record is made which references to the old record.
The closing of the old record is done by filling the field "Removed" with "1" and the field "DateUnlockNew" with the value from field "DateUnlock" of the new record.
The problem is the "DateUnlockNew" isn't filled in a lot of these old records.

I made two changes:
1. Removed the "GPOld" alias from the from-clause. Seeing as i read in the T-SQL reference that, if the same table is referenced twice, one must be without an alias. Is my thinking correct?
At least I have no more error msg's

2. added (GPNew.bdLosgekoppeld=1) to limit the number of rows returned from GPNew... Should speed things up a bit, I think?

Well, thanks for getting me on the right track, Rune! Much appreciated!

Specifies that a table is used to provide the criteria for the update operation. For more information, see FROM.

table_name [ [ AS ] table_alias ]
Is the name of a table to provide criteria for the update operation.
If the table being updated is the same as the table in the FROM clause, and there is only one reference to the table in the FROM clause, table_alias may or may not be specified. If the table being updated appears more than one time in the FROM clause, one (and only one) reference to the table must not specify a table alias. All other references to the table in the FROM clause must include a table alias. "