From: | Andreas Joseph Krogh <andreak(at)officenet(dot)no> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: simple SQL query |
Date: | 2008-10-29 19:58:27 |
Message-ID: | 200810292058.27537.andreak@officenet.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote:
> Hello:
>
>
>
> I have a couple of queries that are giving me headaches.
>
> They are actually very simple, but I do not understand why
>
> I am not getting the expected results. Maybe I need new glasses.
>
> Please be kind.
>
>
>
> The table definitions are below.
>
>
>
> The table TMP_INDEX_MEMBER contains 21057 rows.
>
> These rows contain 3167 distinct ISINs. ISIN is a type of unique
> security identifier.
>
>
>
> This query
>
> select * from security
>
> where securitytypekey NOT IN ( 5,27) and ISIN IN
>
> (select ISIN from tmp_index_member )
>
> returns 3069 rows. This tells me that there are 3069 ISINs
>
> in the SECURITY table. ISINs that I already know about.
>
>
>
>
>
> update tmp_index_member set securitykey = security.securitykey
>
> from security
>
> where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN =
> security.ISIN
>
> results in Query returned successfully: 20545 rows affected, 2169 ms
> execution time.
>
>
>
> There are now 512 row in TMP_INDEX_MEMBER that have not been updated.
>
> OK now the confusion begins.
>
>
>
> I would expect the following query to return 512 rows. It returns zero.
>
> select * from tmp_index_member tim
>
> where tim.ISIN NOT IN
>
> (select distinct sec.ISIN from security sec where securitytypekey NOT
> IN ( 5,27) )
>
>
>
>
>
> I want to add to SECURITY the securities that are new to me. To do this
> I need the above
>
> query to work.
I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like:
... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))...
> Question: does a UNIQUE constraint create an index?
Yes.
> Maybe your fresh eyes will see something obvious.
>
> Many thanks to taking a look at this issue.
--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Duffy | 2008-10-29 20:56:14 | Re: simple SQL query |
Previous Message | Kevin Duffy | 2008-10-29 17:39:42 | simple SQL query |