From: | msi77 <msi77(at)yandex(dot)ru> |
---|---|
To: | Andreas Gaab <a(dot)gaab(at)scanlab(dot)de>,pgsql-sql(at)postgresql(dot)org |
Subject: | Re: is there a distinct function for comma lists ? |
Date: | 2010-09-28 14:00:45 |
Message-ID: | 445681285682445@web147.yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
> And as bonus ... is there a way to find IDs that are in the list but not
> in the table without creating a temporary table and use a join?
Does below satisfy you?
select * from (values (1), (2), (3), (5), (7), (11), (3),
(6), (13), (13), (3), (11)) as X(a)
where a not in(select id from mytable)
> Hi,
> For the problem 1 perhaps something like
> select distinct unnest(ARRAY[ 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ])
> Regards,
> Andreas
> -----Ursprüngliche Nachricht-----
> Von: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] Im Auftrag von Andreas
> Gesendet: Dienstag, 7. September 2010 13:52
> An: pgsql-sql(at)postgresql(dot)org
> Betreff: [SQL] is there a distinct function for comma lists ?
> Hi,
> is there a distinct function for comma separated lists ?
> I sometimes need to update tables where I got a set of IDs, like:
> update mytable
> set someattribute = 42
> where mytable.id in
> ( 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... )
> So there are double entries in the list but in this case its just
> overhead but no problem.
> But for calculated values this would not allways be desirable.
> update mytable
> set someattribute = someattribute + 1
> where mytable.id in
> ( 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... )
> How could I get a distinct list? Those lists can have 2000-3000 IDs
> sometimes.
> One solution was as follows but perhaps there is something more elegant?
> update mytable
> set someattribute = someattribute + 1
> where mytable.id in
> ( select distinct id from mytable where id in ( 1, 2, 3, 5, 7, 11, 3,
> 6, 13, 13, 3, 11 ... ) )
> And as bonus ... is there a way to find IDs that are in the list but not
> in the table without creating a temporary table and use a join?
>
Здесь спама нет http://mail.yandex.ru/nospam/sign
From | Date | Subject | |
---|---|---|---|
Next Message | Tarlika Elisabeth Schmitz | 2010-09-28 20:36:00 | Re: identifying duplicates in table with redundancies |
Previous Message | Oliveiros d'Azevedo Cristina | 2010-09-28 10:34:31 | Re: identifying duplicates in table with redundancies |