Thank you Richard and Elke for helping so fast,
I tried your advices and they work fine. But am I right, that if i.e. TableA has 100.000 entries and TableB 10.000, Than in both versions the subquery "(select TB.FB from TB where TB.Nr = TA.Nr)" will be executed for each entry in TB (so 10.000 times)??? Isn't there a workaround to increase performance by executing this query only once? If TB is a (complex) View-Table I'm coming into performance trouble very quickly Thank's anyway Phil "Zabach, Elke" <[EMAIL PROTECTED]> schrieb am 28.09.05 15:28:09: Richard Martin wrote: > > Hi, > > a few day ago I hit the same problem... > > TA=TableA > TB=TableB > FA=FieldA > FB=FieldB > > This should work (but it takes pretty long): > update TA set (FA)=(select TB.FB from TB where TB.Nr = TA.Nr) > Where TA.Nr in (select TB.Nr from TB) update TA set (FA)=(select TB.FB from TB where TB.Nr = TA.Nr) Where exists (select TB.Nr from TB where TB.Nr = TA.Nr) Plus an index on tb.nr should increase the performance Elke SAP Labs Berlin > > This should be ok but it does not work on MaxDB: > UPDATE TA INNER JOIN TA ON TB.NR = TA.NR > SET TA.FA = TB.FB > > Good Luck > Richard Martin > D-14513 Teltow > > > > Philipp Kemmer wrote: > > > > > Hi, > > > > I really need your help. I have some problems with implementing an > Update of special rows in one of my tables: > > > > I have the following tables: > > > > "T1" with fields called "id" and "value" > > > > and a table "T2" with the fields "id" and "value" > > > > I want to update T1.value with T2.value where T1.id = T2.id, > > > > but only if the T2 contains the id. > > > > Here is the example: > > > > > > > > T1: > > > > id, value > > > > 1, 'A' > > > > 2, 'B' > > > > 3, 'C' > > > > > > > > T2: > > > > id, value > > > > 2, 'X' > > > > > > > > After the update T1 should be > > > > id, value > > > > 1, 'A' > > > > 2, 'X' > > > > 3, 'C' > > > > I first tried > > > > UPDATE "T1" SET "value" = (SELECT "T2"."value" FROM "T2" WHERE "T1"."id" > = "T2"."id") > > > > > > > > But here all rows (exept id=2) are set to NULL. > > > > I think this is not a very complex problem but I need a advice how to > solve it. > > > > > > > > Thanks for any help or advice > > > > Phil > > > > > > > > Verschicken Sie romantische, coole und witzige Bilder per SMS! > > Jetzt bei WEB.DE FreeMail: http://f.web.de/?mc=021193 > > > > > > > > > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Verschicken Sie romantische, coole und witzige Bilder per SMS! Jetzt bei WEB.DE FreeMail: http://f.web.de/?mc=021193
