Update query "updatable query" issue

Question

In Access 2003, I'm trying to take a table and if any Indicator = 1 for ANY line of a given ID, make all lines = 1 for that ID

For Example, if we have:

ID   Indicator
Jack 0 
Jack 0
Jeff 1
Jeff 0
Mark 1
Mark 1

Would become:

ID   Indicator
Jack 0 
Jack 0
Jeff 1
Jeff 1
Mark 1
Mark 1

Since both Jeff and Mark have at least 1 line with Indicator = 1 I want to make ALL their lines = 1.

This is my attempt so far, but I keep getting an error "Operation must use an updateable query" (I think because the select statement is not a table... you can't update a query)

UPDATE Table1 INNER JOIN (SELECT DISTINCT Table1.ID
FROM Table1
WHERE (((Table1.Indicator)=1)))
List ON Table1.ID =List.ID SET Table1.Indicator = 1;

What is a good way to do this quickly and efficiently without making a temporary table? Or would a temporary table be the best/most efficient solution? This will clutter up my database quite a bit since there are 8 lines I have to do this on... the table I'm opperating on is ~300 MB.

Thanks

Answer 1

The simplest solution:

UPDATE TABLE 
   SET indicator = 1
 WHERE id IN ('Jeff', 'Mark') --etc

If you wanted to only update the rows where the value isn't already 1:

UPDATE TABLE 
   SET indicator = 1
 WHERE id IN ('Jeff', 'Mark')
   AND indicator != 1

Answer 2

This is one way to do it:

UPDATE T1 SET Indicator = 1 
WHERE Indicator = 0
AND EXISTS (SELECT * FROM T1 AS T2 WHERE T2.Indicator = 1 AND T1.ID = T2.ID)

Answer 3

Have you tried something like this

UPDATE [Table] SET [Table].[Indicator] = 1, [Table].ID = [ID]
WHERE Exists (SELECT * FROM [Table] t WHERE ID = t.ID and [Table].Indicator = 1);