| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE YTS797 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, yts797.sql |
| -- 59-byte ID |
| -- TEd Version # |
| |
| -- AUTHORIZATION CTS1 |
| set schema CTS1; |
| 0 rows inserted/updated/deleted |
| ij> --O SELECT USER FROM HU.ECCO; |
| VALUES USER; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| CTS1 |
| ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment |
| ROLLBACK WORK; |
| ij> -- date_time print |
| |
| -- TEST:7558 <scalar subquery> in SET of searched update! |
| |
| DELETE FROM TV; |
| 5 rows inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (1,'a'); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (2,'b'); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (3,'c'); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (4,'d'); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TV VALUES (5,'e'); |
| 1 row inserted/updated/deleted |
| ij> DELETE FROM TW; |
| 5 rows inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('b',2); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('g',1); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('f',2); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('h',4); |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO TW VALUES ('i',5); |
| 1 row inserted/updated/deleted |
| ij> --O UPDATE TV AS X |
| UPDATE TV |
| SET B = |
| --O (SELECT D FROM TV AS Y, TW AS Z |
| (SELECT D FROM TV Y, TW Z |
| WHERE Y.A = Z.E |
| AND TV.A = Y.A); |
| ERROR 21000: Scalar subquery is only allowed to return a single row. |
| ij> -- PASS:7558 If ERROR - cardinality violation? |
| --N new error messages are temporarily valid, till we implement this kind of update properly |
| |
| --O UPDATE TV AS X |
| UPDATE TV |
| SET B = |
| --O (SELECT D FROM TV AS Y, TW AS Z |
| (SELECT D FROM TV Y, TW Z |
| WHERE Y.A = Z.E AND Z.E <> 2 |
| AND TV.A = Y.A); |
| 5 rows inserted/updated/deleted |
| ij> -- PASS:7558 If UPDATE completed successfully? |
| |
| SELECT B |
| FROM CTS1.TV |
| WHERE A = 1; |
| B |
| ---- |
| g |
| ij> -- PASS:7558 If B = 'g'? |
| |
| SELECT B |
| FROM CTS1.TV |
| WHERE A = 2; |
| B |
| ---- |
| NULL |
| ij> -- PASS:7558 If B = NULL? |
| |
| SELECT B |
| FROM CTS1.TV |
| WHERE A = 3; |
| B |
| ---- |
| NULL |
| ij> -- PASS:7558 If B = NULL? |
| |
| SELECT B |
| FROM CTS1.TV |
| WHERE A = 4; |
| B |
| ---- |
| h |
| ij> -- PASS:7558 If B = 'h'? |
| |
| SELECT B |
| FROM CTS1.TV |
| WHERE A = 5; |
| B |
| ---- |
| i |
| ij> -- PASS:7558 If B = 'i'? |
| |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 7558 <<< END TEST |
| -- ********************************************* |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |