On Fri, Jan 13, 2006 at 12:11:40AM +0100, me@alternize.com wrote:
> how can something like this be achieved:
>
> UPDATE mytable SET m_list = (SELECT DISTINCT s_id FROM sometable
> WHERE s_id > 6000) WHERE m_id = 10;
In 7.4 and later, if m_list were an array then you could do this:
UPDATE mytable SET m_list = ARRAY(SELECT ...) WHERE ...
> the field m_list would then hold something like '6001, 6002, 6003, 7000',
> which ideally i could later use for something like SELECT * FROM sometable
> JOIN mytable WHERE s_id IN m_list AND m_id = 10;
Again, if m_list were an array then you could do something like this:
SELECT *
FROM sometable AS s
JOIN mytable AS m ON s.s_id = ANY(m.m_list)
WHERE m_id = 10;
> field m_list would idealy be a varchar if possible...
Why? It could be done but an array seems more suitable for what
you're describing.
Is there a reason you want
m_id | m_list
------+-----------------------
10 | {6001,6002,6003,7000}
instead of the more conventional
m_id | s_id
------+------
10 | 6001
10 | 6002
10 | 6003
10 | 7000
?
--
Michael Fuhr
