Since I've got data running through, and it's 4am and I'm a glutton I'll try to explain what I'm doing :) I want to flag records where the are no occurances of 2 different f1's to a f2 and vice versa

lets put letters in f1 and numbers in f2

f1 - a a a b b c d e f g g g h h h h i
f2 - 1 1 1 2 3 4 4 5 6 7 7 8 9 9 9 9 9
f3 - 0 0 0 1 1 1 1 0 0 1 1 1 1 1 1 1 1

"a"'s only match 1's in f2 so they do not get flagged
"b"'s match a 2 and a 3 so it gets flagged
"c" only matches a 4 however 4's match "c" and "d" so it gets flagged
etc etc

hope that explains it better, as I said I'm not looking for a solution to the above, I already have that, it's just what I was doing when I found that an update fails doing the same query where a select is sucessfull, that's what puzzles me.

>yep, same with engine behaviour set to 90.
>
>>it's not so much a question of what I was doing or how to do it, although I just spent 30 seconds trying to figure out if I could have used joins and it made made my ears bleed a little. What I wanted to know is why an update fails where a select suceeds, when in effect they are doing the same thing... ooh I have just noted I've got set enginebehaviour 70 in there, might just test it again with it set to 90.
>>
>>>I am not sure I got the exact idea of what you're trying to do, but you can use INNER JOINs instead, e.g.
>>>

>>>update tablea set field1 = '1 from Table1 t inner join (select ... ) sql1 on t1.f2 = sql2.f2 inner join ...
>>>
>>>

>>>
>>>
>>>>Just a quick one, is Update-SQL really more limited on how complex the expression can be than Select-SQL or am I missing something?
>>>>
>>>>I have a table with 2 populated fields and I want to put a "1" in a 3rd field only when there are not 2 different values in field 1 matching a single value in field 2 or vice versa.
>>>>
>>>>this sort of thing:
>>>>
>>>>1 2 ok
>>>>1 2 ok
>>>>5 5 ok
>>>>2 3 not ok
>>>>3 3 not ok
>>>>4 3 not ok
>>>>4 4 not ok
>>>>
>>>>so step 1 I do:
>>>>

select f1,f2 group by f1,f2 into cursor look_up

>>>>
>>>>then if I do following select, I get a result ok:
>>>>

>>>>SELECT id FROM mytable where;
>>>> f1 in (SELECT f1 from look_up group by f1 having count(*) = 1) AND ;
>>>> f2 in (select f2 from look_up group by f2 having count(*) = 1)
>>>>

>>>>
>>>>so I'd expect to be able to do:
>>>>

>>>>update mytable set flag = "1" where;
>>>> f1 in (SELECT f1 from look_up group by f1 having count(*) = 1) AND ;
>>>> f2 in (select f2 from look_up group by f2 having count(*) = 1)
>>>>

>>>>
>>>>but that gives a sql expression to complex error, I'd expect a simple update like that to work whereever the select works, even if I do the two group selects beforehand so that the two selects in the IN bits are simple it fails, so are the limits really different and if so where can I check out the differences?
>>>>
>>>>Either way it's not a big issue, I just have to do that example select into a cursor and then do:
>>>>

>>>>update mytable set flag = "1" where id in (select id from complexselect)
>>>>

>>>>
>>>>Just curious as I wasted 10 mins looking inquizitivly at the two statements.