Nevermind, I got it working right :o)


Can a field in the result set be updated based on the query result? In other words (I'm trying this unsuccessfully):

UPDATE table1 x SET myfield = 'FANC' 
  WHERE EXISTS
  (SELECT i.* from table1 i
  WHERE (i.field1 = 'FA' and field2 IN('AAA','BBB') AND i.field3 = 0)
     AND NOT EXISTS (SELECT * from table3 a WHERE a.table2id = i.table2id AND a.field4 = i.field4))
     AND (EXISTS (SELECT * from table2 s WHERE s.table2id = i.table2id AND s.fielda <> 'X' 
                   AND s.fieldb <> 'I')
     OR NOT EXISTS (SELECT * from table2 s WHERE s.table2id = i.table2id)) and x.field4=i.field4)

I want to update a field in all the records in table1 that come back in that query (using tsql not fox)...




>You're welcome.
>
>>You're right. I should have caught that. Thanks so much Sergey!
>>
>>
>>>Why repeat the same conditions twice? Try
>>>

>>>SELECT i.* from table1 i
>>>	  WHERE (i.field1 = 'FA' and field2 IN('AAA','BBB') AND i.field3 = 0)
>>>       		AND NOT EXISTS (SELECT * from table3 a WHERE a.table2id = i.table2id AND a.field4 = i.field4))
>>>  			AND (EXISTS (SELECT * from table2 s WHERE s.table2id = i.table2id AND s.fielda <> 'X' AND s.fieldb <> 'I')
>>>    				OR NOT EXISTS (SELECT * from table2 s WHERE s.table2id = i.table2id))
>>>

>>>>I think this works:
>>>>

>>>>SELECT i.* from table1 i
>>>>  WHERE (((i.field1 = 'FA' and (field2 = 'AAA' or field2 = 'BBB') AND i.field3 = 0)
>>>>     AND EXISTS (select * from table2 s where s.table2id = i.table2id AND s.fielda <> 'X'
>>>>                    AND s.fieldb <> 'I')
>>>>     AND NOT EXISTS (SELECT * from table3 a where a.table2id = i.table2id
>>>>                      AND a.field4 = i.field4))
>>>>    OR
>>>>      ((i.field1 = 'FA' and (field2 = 'AAA' or field2 = 'BBB') AND i.field3 = 0)
>>>>            AND NOT EXISTS (select * from table2 s where s.table2id = i.table2id)
>>>>            AND NOT EXISTS (SELECT * from table3 a where a.table2id = i.table2id
>>>>                             AND a.field4 = i.field4)))
>>>>