Re: [sqlite] Update with two tables
You need two UPDATEs to change rows in two tables. BEGIN; UPDATE TabAgeFattProdutt SET Stato ='I' WHERE NumFattAG = -- the INNER JOIN evaluates to a aingle value -- (SELECT NumFatt FROM TabTestaFattAG WHERE NumFatt = '23/12' -- ) ; UPDATE TabTestaFattAG SET StatoFatt = 'Chiusa' WHERE NumFatt = '23/12' ; COMMIT; -Ursprüngliche Nachricht- Von: Leonardodavinci [mailto:alessan...@system-ini.it] Gesendet: Donnerstag, 27. Dezember 2012 16:57 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Update with two tables hi i.m new user with sqlite and now i have this problem with update function: with mysql run this script: UPDATE TabAgeFattProdutt INNER JOIN TabTestaFattAG ON TabAgeFattProdutt.NumFattAG = TabTestaFattAG.NumFatt SET TabAgeFattProdutt.Stato ='I', TabTestaFattAG.StatoFatt = 'Chiusa' WHERE TabTestaFattAG.NumFatt = '23/12' but under sqlite not work!!! i think about the problem is the syntax but where??? thanks Alex -- View this message in context: http://sqlite.1065341.n5.nabble.com/Update-with-two-tables-tp9506p66287.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update with two tables
hi i.m new user with sqlite and now i have this problem with update function: with mysql run this script: UPDATE TabAgeFattProdutt INNER JOIN TabTestaFattAG ON TabAgeFattProdutt.NumFattAG = TabTestaFattAG.NumFatt SET TabAgeFattProdutt.Stato ='I', TabTestaFattAG.StatoFatt = 'Chiusa' WHERE TabTestaFattAG.NumFatt = '23/12' but under sqlite not work!!! i think about the problem is the syntax but where??? thanks Alex -- View this message in context: http://sqlite.1065341.n5.nabble.com/Update-with-two-tables-tp9506p66287.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update with two tables
Unit 5 wrote: --- Jim Dodgen <[EMAIL PROTECTED]> wrote: this should work. UPDATE T1 SET T1.colA = (select T2.colA from T2 where T1.colC = T2.colC), T1.colB = (select T2.colB from T2 where T1.colC = T2.colC) WHERE EXISTS (select * from T2 where T1.colC = T2.colC); Jim, Thanks for your answer. Indeed, it works great. I checked the statement with EXPLAIN but as I am new to SQLite, did not make much sense of the plan. However, it seems like the same sub-select gets executed repeatedly, once for each updated column and once at the end. Does this degrade performance or does the engine optimize these separate statements into one? I ask because the file I need to run this update statement against has several million records. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Unit, You are correct, the subselects are executed multiple times. You can avoid this by using several SQL statements to do the job inside a transaction. First build a temp table with the desired column values for the final table by joining the two existing tables. Then delete all the records from the existing table T1 and copy all the records from the temp table back into T1. Finally, delete the update records from T2, and drop the temp table. begin transaction; create temp table T3 as select case when T2.colC isnull then T1.colA else T2.colA end as colA, case when T2.colC isnull then T1.colB else T2.colB end as colB, T1.colC as colC from T1 left join T2 on T1.colC = T2.colC; delete from T1; insert into T1 select * from T3; delete from T2; drop table T3; commit transaction; This may or may not be any faster depending upon the number of records in T1 and T2. Try it out. Both forms should be much faster if T1 and T2 have an index colC. HTH Dennis Cote
Re: [sqlite] Update with two tables
You could also try this. I imagine it has to do much less internal querying to get the job done. This does assume you have a single column PK, beyond the rowid, that would not change when the other columns changes. INSERT OR REPLACE INTO T1 SELECT * FROM T2; DELETE FROM T1 WHERE pk NOT IN (SELECT pk FROM T2); On 5/9/06, Unit 5 <[EMAIL PROTECTED]> wrote: --- Jim Dodgen <[EMAIL PROTECTED]> wrote: > this should work. > > UPDATE T1 > SET T1.colA = (select T2.colA from T2 where T1.colC > = T2.colC), > T1.colB = (select T2.colB from T2 where T1.colC > = T2.colC) > WHERE EXISTS (select * from T2 where T1.colC = > T2.colC); > Jim, Thanks for your answer. Indeed, it works great. I checked the statement with EXPLAIN but as I am new to SQLite, did not make much sense of the plan. However, it seems like the same sub-select gets executed repeatedly, once for each updated column and once at the end. Does this degrade performance or does the engine optimize these separate statements into one? I ask because the file I need to run this update statement against has several million records. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Update with two tables
this should work. UPDATE T1 SET T1.colA = (select T2.colA from T2 where T1.colC = T2.colC), T1.colB = (select T2.colB from T2 where T1.colC = T2.colC) WHERE EXISTS (select * from T2 where T1.colC = T2.colC); Unit 5 wrote: Hello, I am trying to update values of some columns in a table with fresh data from another table. The two tables have the same column definitions. Is this possible? Even better would be if the two tables could be the same; then I could use table aliases in the update statement. Here is what I would like to do: UPDATE T1 SET T1.colA = T2.colA, T1.colB = T2.colB FROM T1, T2 WHERE T1.colC = T2.colC Thanks in advance! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Update with two tables
Hello, I am trying to update values of some columns in a table with fresh data from another table. The two tables have the same column definitions. Is this possible? Even better would be if the two tables could be the same; then I could use table aliases in the update statement. Here is what I would like to do: UPDATE T1 SET T1.colA = T2.colA, T1.colB = T2.colB FROM T1, T2 WHERE T1.colC = T2.colC Thanks in advance! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
