SQL > SQL scripts > SQL script example

SQL script CREATE, POPULATE, UPDATE and SELECT

To try out the capacity of the PdxEditor SQL script processing, you may try out this example of creating and populating two tables, running a conditioned UPDATE statement and finally displaying the result.

TableA before UPDATE	TableB	TableA after UPDATE

/*
DROP TABLE TableA;
DROP TABLE TableB;
*/
CREATE TABLE TableA
(IDField INTEGER, ValueFieldA INTEGER);
INSERT INTO TableA VALUES (25,3);
INSERT INTO TableA VALUES (36,2);
INSERT INTO TableA VALUES (38,1);

CREATE TABLE TableB
(IDField INTEGER, ValueFieldB INTEGER);
INSERT INTO TableB VALUES (36,287);
INSERT INTO TableB VALUES (37,311);
INSERT INTO TableB VALUES (38,127);

The update statement/script

UPDATE TableA
  SET ValueFieldA =
   (SELECT b.ValueFieldB
    FROM TableB b
    WHERE IDField = TableA.IDField)
-- Prevents NULL'ing fields for non-matching records:
WHERE IDField IN
  (SELECT IDField
   FROM TableB
   WHERE IDField = TableA.IDField
   AND TableA.ValueFieldA <> ValueFieldB
  )
;
SELECT * FROM TableA;