Re: Question to update
Hello Frank. Thanks for your reply. I have following situation: There are two address tables. The tablecard3_nr is my main address table from our company. The dtb_imp2 table contains data from another company. These tables have three columns where I need an exchange of values depends if of the actual date of change. Final I want to have the most actual data in both tables. One field of the external data is use in my card3_nr table. For adjustment I have only the fields vorname(firstname), name,strasse(street),plz(zip-code),ort(city) and gebdat(birthday) The first step is the adjustment of the dtb_imp2 table, because when there is an error during update I can delete all data an reload it from a backup file. Best regards. Dirk FRANK schrieb: > 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 > > > > ------=_Part_40812_6038739.1168028418574 > Content-Type: text/html; charset=ISO-8859-1 > X-Google-AttachSize: 2148 > > <div>HI, Dirk Emmermacher<br> </div> > <div> </div> > <div>Please add the conditions( to specify eaxctly what kind of rows you want to update) at the second "WHERE" clause.</div> > <div> </div> > <div>Frank</div> > <div><br><br> </div> > <div><span class="gmail_quote">On 5 Jan 2007 01:30:12 -0800, <b class="gmail_sendername"><a href="mailto:emmermacher@hotmail.com">emmermacher@hotmail.com</a></b> <<a href="mailto:emmermacher@hotmail.com">emmermacher@hotmail.com > </a>> wrote:</span> > <blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Hello list.<br><br>I try to update some columns with following command:<br><br>update dtb_imp2<br>set mitglieds_nr > <br>= (select mitglieds_nr from card3_nr<br>where card3_nr.vname = dtb_imp2.vorname and<br>card3_nr.name = dtb_imp2.nachname and<br>card3_nr.strasse = dtb_imp2.strasse and<br>card3_nr.plz = dtb_imp2.plz and<br>card3_nr.ort = dtb_imp2.ort and > <br>card3_nr.gebdat = dtb_imp2.gebdat and<br>card3_nr.card_datum > dtb_imp2.gueltig_bis )<br>where vorname in (select vname from card3_nr)<br>and nachname in (select name from card3_nr)<br>and strasse in (select strasse from card3_nr) > <br>and plz in (select plz from card3_nr)<br>and ort in (select ort from card3_nr)<br>and gebdat in (select gebdat from card3_nr)<br><br>In the table dtb_imp2 are 2900 rows and I know that there are 38 rows<br>to update with new values here. When I run this command, all rows are > <br>updated here.<br>How can I use the fields card3_nr.card_datum and dtb_imp2.gueltig_bis<br>to update only the rows I need to update? My IDS is version 7.30 (For<br>the moment the time is short for an upgrade :( )<br><br> > Thanks in advance for your tips.<br><br>Best regards from Hannover, Germany<br><br>Dirk Emmermacher<br><br>_______________________________________________<br>Informix-list mailing list<br><a href="mailto:Informix-list@iiug.org"> > Informix-list@iiug.org</a><br><a href="http://www.iiug.org/mailman/listinfo/informix-list">http://www.iiug.org/mailman/listinfo/informix-list</a><br></blockquote></div><br>; > > ------=_Part_40812_6038739.1168028418574--