Wiki Home

Update SQL


Namespace: Wiki
Updates records in a table with new values. Visual FoxPro Syntax:
UPDATE [DatabaseName1!]TableName1
SET Column_Name1 = eExpression1
  [, Column_Name2 = eExpression2 ...]
  WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

Invariably slower than the VFP Replace Command, Update SQL has the advantage of not locking the file for multiple updates. VFP Help suggests that for maximum performance, open the table for exclusive use or use FLOCK( ) to lock the table.-- Steven Black

So, what's faster gentlemen? Of course a replace x with y is faster than an update since there is no field I/O or pointer moving going on... but how about:

REPLACE FOR (or REPLACE ALL)
vs
UPDATE WHERE (with no where)
I posted my .02 in the REPLACE page but someone must not have liked it because it "poofed" --- John Koziol
I liked it, but I moved it to here (where it better belonged, as someone suggested) and, once here, it wasn't anything new, so it got refactored in the next pass.-- Steven Black
Particularly handy when you use a sub-query, like so:
UPDATE [Table1] ;
   SET [Table1.SomeField] = [SomeValue] ;
   WHERE [Table1.SelectionField] IN ;
      (SELECT [Table2.MatchingField] FROM [Table2])

SQL Update is very handy, but its SET expression limits you to either a static assignment value or writing a function which returns an appropriate value. I would love to have a way of determining the value dependent on the the current record. If anyone knows a good (i.e. fast) way of doing this, post it. Please! William OConnor
I suppose you'd like UPDATES based on subqueries, something like:

UPDATE InvHead ;
   SET TotShipWgt  = ;
      (SELECT SUM(Qty_Picked*UnitWeight) AS LineWeight ;
              FROM InvLines, Inventory ;
              WHERE InvLines.PartNo = Inventory.PartNo AND;
                    InvLines.InvNo = InvHead.InvNo AND 
                    ItemPicked ;
              GROUP BY InvNo) ;
   WHERE OKToShip AND NOT InvShipped

I resort to pre-querying and then using an IIF() to do what is needed:

SELECT Sum(Qty_Picked * UnitWeight) AS LineWeight, InvHead.InvNo ;
  FROM InvHead, InvLines, Inventory ;
 WHERE InvHead.InvNo = InvLines.InvNo AND ;
       InvLines.PartNo = Inventory.PartNo AND ;
       ItemPicked AND OKToShip AND NOT InvShipped ;
 GROUP BY InvHead.InvNo ;
 INTO CURSOR PendShipWgt

INDEX ON InvNo TAG INVNO

UPDATE InvHead ;
   SET TotShipWgt = IIF(SEEK(InvHead.InvNo,'PendShipWgt'),PendShipWgt.LineWeight,0) ;
 WHERE OKToShip AND NOT InvShipped


A little bit of work, but reasonably clear, portable, and most importantly, decipherable by the poor shmo who has to fix it later! -- Ed Rauh


Category VFP Commands
( Topic last updated: 1999.11.17 09:13:29 PM )