Update with Inner join on same table

[stracker.phil]

Hi!

I try to solve following situation:

The table looks like this:

pattern_id (int), sub_id (int), orderId (int), fieldname (varchar), value (varchar)

Now I wrote a query to get a list of all fieldnames, that come (by means of order) directly before the fieldname "Year" - for this i use an inner join on the same table like in this statement (info - in the table are multplie fields with name="Year", but with different sub_id):

SELECT	fieldname, value
FROM TPattern AS a
    INNER JOIN TPattern AS b ON (a.Pattern_Id=b.Pattern_Id AND a.sub_id=b.sub_id)
WHERE (a.Fieldname='Year')
    AND (b.OrderID = (a.OrderID-1))

Now I need to get an Update that changes the field "value" of the result of above query. I tried to do so with the follwing query, but it did not work.

UPDATE TPattern
    SET value="valid"
FROM TPattern AS a
    INNER JOIN TPattern AS b ON (a.Pattern_Id = b.Pattern_Id)
WHERE (a.Fieldname='Year')
    AND (b.OrderID = (a.OrderID-1))

Thanks for suggestions on how to solve this.
Philipp

[stracker.phil]

I could solve this issue by using a view of the pattern table like in following code:

/* View as help for intra-table comparison */
CREATE VIEW VPattern
AS
SELECT * FROM TPattern
GO

/* Make changes */
UPDATE TPattern
    SET value="valid"
FROM TPattern
    INNER JOIN TPattern AS b
    ON (TPattern.Pattern_Id=b.Pattern_Id AND TPattern.sub_id=b.sub_id)
WHERE (TPattern.Fieldname = 'Year')
    AND (b.OrderID = (TPattern.OrderID-1))

/* Delete view again since it was only needed for the update */
DROP VIEW VPattern

So that's it... :-)