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)...
>>>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))) >>>>