× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: Re: How to insert/update records using SQL?
  • From: Pete Hall <pbhall@xxxxxxxxxx>
  • Date: Thu, 07 Jun 2001 22:25:00 -0500

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
+---

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.