Re: "UPDATE" format, need help
OK -- I was challenged.
No, my original solution does not work in all possible cases, but the
problem is nothing to do with the presence or absence of the METRCV table
in the SELECT statement embedded in the SET clause. Specifically, it does
not work correctly when there is a row in the METRCV table for which there
is no corresponding row in the METCLS table. This is probably a
referential integrity constraint violation (to judge from the cardinality
information in the original problem statement), but can be coded around by
adding a WHERE clause to the UPDATE statement I proposed:
UPDATE metrcv
SET (nr_frcv_dt, nr_frcv_tm) =
((SELECT metcls.nc_log_dt, metcls.nc_log_tm
FROM metcls
WHERE metcls.nc_contact_id = metrcv.nr_contact_id))
WHERE nr_contact_id IN (SELECT DISTINCT nc_contact_id FROM metcls);
Given the sample table and data shown below, I believe this UPDATE statement
produces the correct answer. The SELECT statement in the SET clause is a
correlated sub-query.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
-- Data assumes DBDATE=dmy4/ or thereabouts.
CREATE TEMP TABLE metrcv
(
nr_contact_id INTEGER NOT NULL,
nr_frcv_dt DATE NOT NULL,
nr_frcv_tm DATETIME HOUR TO SECOND NOT NULL
);
CREATE TEMP TABLE metcls
(
nc_contact_id INTEGER NOT NULL,
nc_log_dt DATE NOT NULL,
nc_log_tm DATETIME HOUR TO SECOND NOT NULL
);
INSERT INTO metrcv VALUES(1, "10nov94", DATETIME(10:00:00) HOUR TO SECOND);
INSERT INTO metrcv VALUES(1, "10nov94", DATETIME(10:00:01) HOUR TO SECOND);
INSERT INTO metrcv VALUES(1, "10nov94", DATETIME(10:00:02) HOUR TO SECOND);
INSERT INTO metrcv VALUES(1, "10nov94", DATETIME(10:00:03) HOUR TO SECOND);
INSERT INTO metrcv VALUES(1, "10nov94", DATETIME(10:00:04) HOUR TO SECOND);
INSERT INTO metrcv VALUES(2, "10nov94", DATETIME(10:00:00) HOUR TO SECOND);
INSERT INTO metrcv VALUES(2, "10nov94", DATETIME(10:00:01) HOUR TO SECOND);
INSERT INTO metrcv VALUES(2, "10nov94", DATETIME(10:00:02) HOUR TO SECOND);
INSERT INTO metrcv VALUES(3, "10nov94", DATETIME(10:00:03) HOUR TO SECOND);
INSERT INTO metrcv VALUES(3, "10nov94", DATETIME(10:00:04) HOUR TO SECOND);
INSERT INTO metcls VALUES(1, "12sep95", DATETIME(13:14:15) HOUR TO SECOND);
INSERT INTO metcls VALUES(2, "13sep95", DATETIME(20:34:45) HOUR TO SECOND);
INSERT INTO metcls VALUES(4, "14sep95", DATETIME(23:44:55) HOUR TO SECOND);
Data in METRCV after the update:
1 12/09/1995 13:14:15
1 12/09/1995 13:14:15
1 12/09/1995 13:14:15
1 12/09/1995 13:14:15
1 12/09/1995 13:14:15
2 13/09/1995 20:34:45
2 13/09/1995 20:34:45
2 13/09/1995 20:34:45
3 10/11/1994 10:00:03
3 10/11/1994 10:00:04
>From: "Huddleston, Joe" <huddles@emspo01.hasting.com>
>Date: Tue, 12 Sep 95 13:40:00 PDT
>X-Informix-List-Id: <list.7428>
>
>except, you must add the metrcv table to the from list in the subquery.
> then, your problem is that you can't have the table you're updating in a
>subquery...
>
> -joe-
>(huddles@hasting.com)
>
> ----------
>
>(1) You cannot have ORDER BY clauses in FOR UPDATE cursors.
>(2) You cannot have joins in FOR UPDATE cursors.
>(3) You should be able to do the whole thing in one statement, which
> should be more cost-effective -- less traffic between application
> and database.
>
> UPDATE metrcv
> SET (nr_frcv_dt, nr_frcv_tm) =
> ((SELECT metcls.nc_log_dt, metcls.nc_log_tm
> FROM metcls
> WHERE metcls.nc_contact_id = metrcv.nr_contact_id))
>
> No, I don't have a fetish for double parentheses -- they are a
> neccessary part of the syntax. And No, I haven't actually run this
> through any program or tested it in any way, so there could be an
> unexpected gotcha, but I think that will work as it is.
>
>Yours,
>Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
>
>}From: rjc@cbnews.cb.att.com (robert.cook)
>}Date: Tue, 12 Sep 1995 14:33:45 GMT
>}X-Informix-List-Id: <news.16918>
>}
>} I and several others have experienced problems trying to use the UPDATE
>}function in 4gl. Any assistance is appreciated.
>}
>} I am trying to update a date & time field in one table with the date &
>time
>}data of another table where the ticket numbers are the same.
>}
>}DECLARE fupd_curs CURSOR FOR
>}SELECT nc_contact_id, nc_log_dt, nc_log_tm,
>} nr_contact_id, nc_frcv_dt, nc_frcv_tm
>}FROM metcls, metrcv
>}WHERE nc_contact_id = nr_contact_id
>}ORDER BY nr_contact_id -- There may be multiply nr_contact_id's,
>but
>} only one nc_contact_id
>}
>}FOR UPDATE
>} FOREACH fupd_curs INTO upd_rec
>}
>} UPDATE metrcv
>} SET nr_frcv_dt = metcls.nc_log_dt,
>} nr_frcv_tm = metcls.nc_log_tm
>} WHERE CURRENT OF fupd_curs
>} END FOREACH
>}
>}Not real exact, but I can't get it to work following any of the examples in
>the
>}book. Also, there are no examples showing joined tables.
>}
>}TNKS, Robert
>