VFP9 SQL Enhancements (by yag)

  • Article

I was reading the Universal Thread (one of the many great 3rd party sites I go through every week) and came across a question by Kevin Goff who wanted to know more about the SQL enhancements coming in Visual FoxPro 9. In addition to the information that Ken has discussed in his February letter, I figured that I’d put down a few more samples.

Scalar subqueries (subquery in SELECT list)
SELECT T1.f1, (SELECT f2 FROM foo2 T2 WHERE T2.f1=T1.f1) AS foo2 ; 
  FROM foo1 T1

Subquery in UPDATE SET list
UPDATE foo1 SET f2=100+(SELECT f2 FROM foo2 ;
  WHERE foo2.f1=foo1.f1) WHERE f1>5

Correlated UPDATE and DELETE
UPDATE foo1 SET foo1.f2=foo2.f2, f3=foo2.f2*10 FROM foo2 ;
  WHERE foo1.f1>5 AND foo2.f1=foo1.f1

These enhancements, when combined with some of the new datatypes like varchar and varbinary give VFP developers who work with SQL Server an even smoother experience.