How Update?

Hello, I have have two tables Tab1 A, B, C Tab2 A, D,E The Tables are 1:1 related with edenticale field A (Articlenumber) Now I must copy the values of D in B How should I create my UPDATE statement in dbacces? Greating Ralf

Update tab2 set d=(select b from tab1 where tab2.a=tab1.a) where 1=1; This assumes you are dealing with less than millions of rows. If the datasets are very large, then I wrote up some techniques to deal with them some time back. See http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/pa rker/0502parker.html Example 2 - DSS Updates, perhaps not practical in your situation, but it may give you ideas. cheers j. (back from vaca) Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Ralf Hackmann Sent: Saturday, August 01, 2009 3:40 PM To: informix-list@iiug.org Subject: How Update? Hello, I have have two tables Tab1 A, B, C Tab2 A, D,E The Tables are 1:1 related with edenticale field A (Articlenumber) Now I must copy the values of D in B How should I create my UPDATE statement in dbacces? Greating Ralf _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Hi Jack, thanks for You fast answer On 1 Aug., 21:57, "Jack Parker" <jack.park...@verizon.net> wrote: > Update tab2 > set d=(select b from tab1 where tab2.a=tab1.a) > where 1=1; Than in my example it should be: Update tab1 set d=(select b from tab2 where tab2.a=tab1.a) where 1=1; !? > > This assumes you are dealing with less than millions of rows.  If the > datasets are very large, then I wrote up some techniques to deal with them > some time back.  Seehttp://www.ibm.com/developerworks/data/zones/informix/library/techart... > rker/0502parker.html Example 2 - DSS Updates, perhaps not practical in your > situation, but it may give you ideas. I have habe apr. 30000row in Tab1 and tab2 Greetings Ralf

On Aug 1, 12:57 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > Update tab2 > set d=(select b from tab1 where tab2.a=tab1.a) > where 1=1; > > This assumes you are dealing with less than millions of rows.  If the > datasets are very large, then I wrote up some techniques to deal with them > some time back.  Seehttp://www.ibm.com/developerworks/data/zones/informix/library/techart... > rker/0502parker.html Example 2 - DSS Updates, perhaps not practical in your > situation, but it may give you ideas. It is interesting; I read the question as wanting to update Tab1.B with the values from Tab2.D, but others read it the other way round...I'll go with the flow! This is dangerous - if there are rows in Tab2 with no matching row in Tab1, then the WHERE 1=1 clause will cause the unmatched rows to be updated with NULL. Whether this is a real problem depends on whether the 1:1 relationship is strictly 1:1 or is some variant on '0 or 1 to 0 or 1'. The MERGE statement does not run into this problem. To avoid the problem in the UPDATE, include a condition in the main WHERE clause such as: UPDATE tab2 SET d=(SELECT b FROM tab1 WHERE tab2.a=tab1.a) WHERE A IN (SELECT A FROM tab1); Or, if my reading of the question is more nearly on target: UPDATE Tab1 SET B = (SELECT D FROM Tab2 WHERE Tab1.A = Tab2.A) WHERE A IN (SELECT A FROM Tab2); > -----Original Message----- > From: ... On Behalf Of Ralf Hackmann > Sent: Saturday, August 01, 2009 3:40 PM > > I have have two tables > > Tab1 > A, B, C > > Tab2 > A, D,E > > The Tables are 1:1 related with edenticale field A (Articlenumber) > > Now I must copy the values of D in B > > How should I create my UPDATE statement in dbaccess? -=JL=-

On 2 Aug., 10:39, Jonathan Leffler <jonathan.leff...@gmail.com> wrote: > On Aug 1, 12:57 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > > > This is dangerous - if there are rows in Tab2 with no matching row in > Tab1, then the WHERE 1=1 clause will cause the unmatched rows to be > updated with NULL.  Whether this is a real problem depends on whether > the 1:1 relationship is strictly 1:1 or is some variant on '0 or 1 to > 0 or 1'. > The Tab1 and Tab2 are in real 2 Tables 6 Tables of a stock list in our ERP system , which are strctly 1:1 The stock list is separated in 6 tables so that the different divisions (purchase, sales, stock ...) can edit an record and not block the record for other. My goal was, to make 2 field edentical, who are in different tabels of the stock list Greeting Ralf