Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Update more limited than select?
Message
 
 
To
All
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Update more limited than select?
Miscellaneous
Thread ID:
01291690
Message ID:
01291690
Views:
56
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.
Next
Reply
Map
View

Click here to load this message in the networking platform