Question to update

Hello list. I try to update some columns with following command: update dtb_imp2 set mitglieds_nr = (select mitglieds_nr from card3_nr where card3_nr.vname = dtb_imp2.vorname and card3_nr.name = dtb_imp2.nachname and card3_nr.strasse = dtb_imp2.strasse and card3_nr.plz = dtb_imp2.plz and card3_nr.ort = dtb_imp2.ort and card3_nr.gebdat = dtb_imp2.gebdat and card3_nr.card_datum > dtb_imp2.gueltig_bis ) where vorname in (select vname from card3_nr) and nachname in (select name from card3_nr) and strasse in (select strasse from card3_nr) and plz in (select plz from card3_nr) and ort in (select ort from card3_nr) and gebdat in (select gebdat from card3_nr) In the table dtb_imp2 are 2900 rows and I know that there are 38 rows to update with new values here. When I run this command, all rows are updated here. How can I use the fields card3_nr.card_datum and dtb_imp2.gueltig_bis to update only the rows I need to update? My IDS is version 7.30 (For the moment the time is short for an upgrade :( ) Thanks in advance for your tips. Best regards from Hannover, Germany Dirk Emmermacher

emmermacher@hotmail.com wrote: > Hello list. > > I try to update some columns with following command: > > update dtb_imp2 > set mitglieds_nr You need to add this select: > = (select mitglieds_nr from card3_nr > where card3_nr.vname = dtb_imp2.vorname and > card3_nr.name = dtb_imp2.nachname and > card3_nr.strasse = dtb_imp2.strasse and > card3_nr.plz = dtb_imp2.plz and > card3_nr.ort = dtb_imp2.ort and > card3_nr.gebdat = dtb_imp2.gebdat and > card3_nr.card_datum > dtb_imp2.gueltig_bis ) as an exists in the where clause below, otherwise you'll set lots of mitglieds_nrs to null. > where vorname in (select vname from card3_nr) > and nachname in (select name from card3_nr) > and strasse in (select strasse from card3_nr) > and plz in (select plz from card3_nr) > and ort in (select ort from card3_nr) > and gebdat in (select gebdat from card3_nr) > > In the table dtb_imp2 are 2900 rows and I know that there are 38 rows > to update with new values here. When I run this command, all rows are > updated here. > How can I use the fields card3_nr.card_datum and dtb_imp2.gueltig_bis > to update only the rows I need to update? My IDS is version 7.30 (For > the moment the time is short for an upgrade :( ) > > Thanks in advance for your tips. > > Best regards from Hannover, Germany > > Dirk Emmermacher

HI, Dirk Emmermacher Please add the conditions( to specify eaxctly what kind of rows you want to update) at the second "WHERE" clause. Frank On 5 Jan 2007 01:30:12 -0800, emmermacher@hotmail.com < emmermacher@hotmail.com> wrote: > > Hello list. > > I try to update some columns with following command: > > update dtb_imp2 > set mitglieds_nr > = (select mitglieds_nr from card3_nr > where card3_nr.vname = dtb_imp2.vorname and > card3_nr.name = dtb_imp2.nachname and > card3_nr.strasse = dtb_imp2.strasse and > card3_nr.plz = dtb_imp2.plz and > card3_nr.ort = dtb_imp2.ort and > card3_nr.gebdat = dtb_imp2.gebdat and > card3_nr.card_datum > dtb_imp2.gueltig_bis ) > where vorname in (select vname from card3_nr) > and nachname in (select name from card3_nr) > and strasse in (select strasse from card3_nr) > and plz in (select plz from card3_nr) > and ort in (select ort from card3_nr) > and gebdat in (select gebdat from card3_nr) > > In the table dtb_imp2 are 2900 rows and I know that there are 38 rows > to update with new values here. When I run this command, all rows are > updated here. > How can I use the fields card3_nr.card_datum and dtb_imp2.gueltig_bis > to update only the rows I need to update? My IDS is version 7.30 (For > the moment the time is short for an upgrade :( ) > > Thanks in advance for your tips. > > Best regards from Hannover, Germany > > Dirk Emmermacher > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >