Re: How to do an update with a join??
>From: thompson@netcom.com (Eric Thompson)
>Subject: How to do an update with a join??
>Date: Wed, 1 Dec 1993 21:39:48 GMT
>X-Informix-List-Id: <news.4979>
>
>(Using ISQL 4.10 on a DG/UX box)
>
>EXAMPLE: Table A has many columns two of which are "ss_number" and "name".
> Table B has ONLY two columns, which are "ss_number" and "name". Table
> B contains updated name information for a portion of the rows in table A.
> How can I tell Informix to update the names in Table A from Table where
> the ss_numbers are the same? This doesn't work (obviously) but it seems
> like I should be able to do something like this:
>
> update A
> set A.name = B.name
> where A.ss_number = B.ss_number
This works. Whether it is efficient or not is another matter, but it works.
UPDATE A
SET Name = (SELECT B.Name FROM B WHERE B.SS_Number = A.SS_Number)
WHERE SS_Number IN (SELECT B.SS_Number FROM B)
The 2nd where clause ensures that the only rows updated in table A are the
ones with an entry in table B; otherwise, the rows in A not listed in B
would end up with a null name, which is unlikely to be the desired result.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
PS: Complete test script follows:
CREATE TABLE b (ss_number INTEGER, name CHAR(20));
CREATE TABLE a (ss_number INTEGER, name CHAR(20), otherstuff CHAR(20));
INSERT INTO a VALUES (1, "MMMMMM", "Widgety Picks");
INSERT INTO a VALUES (2, "NNNNNN", "Fidgety Ticks");
INSERT INTO b VALUES (1, "ZZZZZZ");
-- Correct
UPDATE A
SET Name = (SELECT B.Name FROM B WHERE B.SS_Number = A.SS_Number)
WHERE SS_Number IN (SELECT B.SS_Number FROM B);
SELECT * FROM a;-- Duff
UPDATE A
SET Name = (SELECT B.Name FROM B WHERE B.SS_Number = A.SS_Number);
SELECT * FROM a;
DROP TABLE a;
DROP TABLE b;