Thread: Finding entries not in table..differnce?
I need to write a quick function that tells me all of the entriles in table that are not in table2. The tables are copies of each other, but 1 has been updated. I know this is easy, but I am running on little sleep :) I want to due something like select prodlang.prodlsku from prodland,prodlang2 WHERE prodlang.prodlsku != prodlang2.prodlsku But of course I would get every record, several times since at some point the sku does not equal another sku. I tried !!= (NOT IN) but it did not like that at all, since sku is a char field. I am running an older version of postgress on this server, I do not know if that is important. -- Zot O'Connor http://www.ZotConsulting.com http://www.WhiteKnightHackers.com
Assuming that you don't have nulls in prodlang.prodlsku, this should probably work, although I haven't tried it for real. SELECT prodlang.prodlsku FROM prodlang WHERE NOT EXISTS (SELECT * FROM prodlang2WHERE prodlang2.prodlsku=prodlang.prodlsku); If you do have nulls, the inner select probably needs to be something like: (SELECT * FROM prodlang2WHERE prodlang2.prodlsku=prodlang.prodlskuor (prodlang2.prodlsku is null and prodlang.prodlsku isnull)) ----- Original Message ----- From: "Zot O'Connor" <zot@zotconsulting.com> To: "postgres sql" <pgsql-sql@hub.org> Sent: Monday, July 10, 2000 5:29 PM Subject: [SQL] Finding entries not in table..differnce? > I need to write a quick function that tells me all of the entriles in > table that are not in table2. > > The tables are copies of each other, but 1 has been updated. I know > this is easy, but I am running on little sleep :) > > I want to due something like > > select prodlang.prodlsku from prodland,prodlang2 WHERE prodlang.prodlsku > != prodlang2.prodlsku > > But of course I would get every record, several times since at some > point the sku does not equal another sku. > > I tried !!= (NOT IN) but it did not like that at all, since sku is a > char field. > > I am running an older version of postgress on this server, I do not know > if that is important.
If I understand you exactly, you may use except: select distinct * from prodlang except select distinct * from prodlang2 gives you the records which exist in prodlang and do not exist in prodlang2. So you get all the records in prodlang which are newly inserted or updated. regards erol Zot O'Connor wrote: > > I need to write a quick function that tells me all of the entriles in > table that are not in table2. > > The tables are copies of each other, but 1 has been updated. I know > this is easy, but I am running on little sleep :) > > I want to due something like > > select prodlang.prodlsku from prodland,prodlang2 WHERE prodlang.prodlsku > != prodlang2.prodlsku > > But of course I would get every record, several times since at some > point the sku does not equal another sku. > > I tried !!= (NOT IN) but it did not like that at all, since sku is a > char field. > > I am running an older version of postgress on this server, I do not know > if that is important. > > -- > Zot O'Connor > > http://www.ZotConsulting.com > http://www.WhiteKnightHackers.com