Re: How to insert/update records using SQL?

[Pete Hall <pbhall@xxxxxxxxxx>]

At 08:54 06/07/2001, Art Tostaine wrote:
>I have to update a table on SQL/Server from the Iseries.  I have the
>connectivity issues resolved.
>
>Using a sql statement, how do INSERT or UPDATE the records that already
>exist.
>
>In RPG, I would CHAIN, and either WRITE or UPDATE if %FOUND.  Does a similar
>function exist in SQL?
>
>What is the standard or preferred method of doing a mass update?
>
>My SQL table is basically,
>
>CustId
>OpenAr
>Open30
>Open60
>Open90
>Open120

I don't know about "standard". Maybe it doesn't apply to SQL, but you could 
do updates with

  UPDATE CustomerAging
  SET OpenAr = :OpenBalance,
      Open30 = :Over30Balance,
  ...
  WHERE CustId = :CustomerId

The SQL Code will tell you if the row exists.

You can do inserts similarly if the update failed:

  INSERT INTO CustomerAging
  Values(:CustomerId,
        :OpenBalance,
        :Over30Balance,
  ...                  )

You could also do something like this:
  INSERT INTO CustomerAging
  SELECT DISTINCT
   CustNum,
   0,
   0,
   0,
   0,
   0
  FROM CustomerTransactions
  WHERE (NOT EXISTS (
    SELECT * FROM CustomerAging
    WHERE CustNum = CustId))

Then do updates to load the balance values, since the inserts are already done.

You can update a single summary field:

UPDATE CustomerAging
   SET OpenBalance = (SELECT SUM(TranAmount)
                      FROM CustomerTransactions
                      WHERE CustNum = CustomerId)

You could probably do all of the balance buckets at once, but I usually try 
to avoid that because it might cause the optimizer to blow a fuse.

You could probably also get away with something like this if you wanted to 
rebuild the entire table:

  INSERT INTO CustomerAging
  SELECT CustNum,
        SUM(TranAmount),
        SUM(CASE WHEN MAX(DAYS(TranDate) - DAYS(CURRENT DATE) + 30, 0) = 0 
THEN TranAmount ELSE 0 END),
        SUM(CASE WHEN MAX(DAYS(TranDate) - DAYS(CURRENT DATE) + 60, 0) = 0 
THEN TranAmount ELSE 0 END),
        SUM(...
  GROUP BY CustNum

Syntax might be off a little, but it's reasonably close. Although this 
might seem like more work, it only requires processing the transaction file 
once, and might actually be the most efficient way.

Or, you could use an updatable cursor, or you could get more creative...


Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---