The mathlab function setdiff(a,b) returns the rows from a that are not in b. The equivalent SQL (assuming identical tables a and b) would be
SELECT <primary key> FROM a EXCEPT SELECT <primary key> FROM b You can then INSERT INTO b SELECT * FROM a WHERE <primary key> IN (SELECT <primary key> FROM a EXCEPT SELECT <primary key> FROM b); DELETE FROM a WHERE <primary key> IN (SELECT <primary key> FROM a EXCEPT SELECT <primary key> FROM b); -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Keith Medcalf Gesendet: Mittwoch, 18. September 2019 17:12 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] How to do setdiff but update the original table by the result? On Wednesday, 18 September, 2019 08:38, Peng Yu <pengyu...@gmail.com> wrote: >I'd like to perform setdiff. I think this should be relevant. But it >does not update the original table. I want to update the original table >by the result. Is there a command to do so? Thanks. UPDATE ... Please define what is "setdiff". Please define what you mean by "update the original table by the result". For example: I want to find all the rows in table2 that are not in table1 and then insert those rows into table1. I want to find all the rows in table1 that are not in table2 and then delete those rows from table1. Or something to that effect. You need to be very explicit. There is no magic. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
