Ознакомление с возможностями инструментального средства Informix SQL, страница 3

/*------------------------------------------------------------------------------------*/

/*Fill the table j*/

INSERT INTO j (n_good,name,city) VALUES ("J1","Sorter","Paris");

INSERT INTO j (n_good,name,city) VALUES ("J2","Perforator","Rome");

INSERT INTO j (n_good,name,city) VALUES ("J3","Reader","Bonn");

INSERT INTO j (n_good,name,city) VALUES ("J4","Console","Bonn");

INSERT INTO j (n_good,name,city) VALUES ("J5","Selecting machine","London");

INSERT INTO j (n_good,name,city) VALUES ("J6","Terminal","Oslo");

INSERT INTO j (n_good,name,city) VALUES ("J7","Ribbon","London");

/*-------------------------------------------------------------------------------------*/

/*Fill the table spj*/

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S1","P1","J1",200);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S1","P1","J4",700);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S2","P3","J1",400);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S2","P3","J2",200);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S2","P3","J3",200);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S2","P3","J4",500);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S2","P3","J5",600);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S2","P3","J6",400);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S2","P3","J7",800);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S2","P5","J2",100);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S3","P3","J1",200);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S3","P4","J2",500);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S4","P6","J3",300);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S4","P6","J7",300);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P2","J2",200);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P2","J4",100);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P5","J5",500);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P5","J7",100);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P6","J2",200);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P1","J4",100);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P3","J4",200);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P4","J4",800);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P5","J4",400);

INSERT INTO spj (n_post,n_det,n_good,number) VALUES ("S5","P5","J4",400);

/* Last record in spj is wrong record, because instead of 400 in field number

there must be 500 and instead of "P5" in field n_good there must be "P6" */

/*There are two ways to correct it*/

SELECT rowid, * FROM spj

/* Remember the last rowid*/

/* First way to correct it, delete record and then insert again */

DELETE FROM spj WHERE rowid = :parameter;

INSERT INTO spj n_post,n_det,n_good, number VALUES ("S5","P6","J4",500);

/* Second way is to update existing record */

UPDATE spj SET n_det = "P6", number = 500 WHERE rowid = :parameter;

Синтаксис операторов:

DELETE FROM <имя таблицы> WHERE <условие>

UPDATE <имя таблицы> SET <список вида <поле> =<выражение>> WHERE <условие>