Re: If it Exists... Update. If it DNE... insert.

From: --CELKO-- <JOE.CELKO_at_TRILOGY.COM>
Date: 12 Jul 2001 11:31:14 -0700
Message-ID: <33254b33.0107121031.6e24e75a@posting.google.com>

Tables have rows and not records -- big difference. The stadnard SQl for this would be like this:

 BEGIN ATOMIC
 UPDATE Foo

    SET a = COALESCE((SELECT a

                        FROM Bar)
                       WHERE Foo.keycol = Bar.keycol), Foo.a),
        b = COALESCE((SELECT b
                        FROM Bar)
                       WHERE Foo.keycol = Bar.keycol), Foo.b),
        ...
 WHERE EXISTS(SELECT *
                FROM Bar)
               WHERE Foo.keycol = Bar.keycol); 

 INSERT INTO Foo (a, b, ..)
 SELECT a, b, ..

    FROM Bar
   WHERE Foo.keycol = Bar.keycol;
 END; The predicates in the statements will be as fast as the same predicates in a 4GL. Received on Thu Jul 12 2001 - 13:31:14 CDT