Thread: No JOINs in UPDATE ... FROM?
Folks,
In 7.1 RC2, I've been trying to use the following JOIN syntax:
UPDATE assignments SET status = -1
FROM assignments JOIN orders ON assignments.order_usq = orders.usq
WHERE orders.status = -1;
However, I get an error of "Relation assignments referenced twice in
query."
Now, I can (and have) re-phrase the query so that PostgreSQL will
accept it. However, I was under the impression that the above was
standard SQL92. Am I mistaken? Or is this form something that just
hasn't been implemented yet?
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
How I can return random N rows from my select stmt? like: e.g. what my selectee is a set of 1000 rows, I want randomly pickup 100 of 1000. thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com
Hi! > How I can return random N rows from my select stmt? > like: > e.g. what my selectee is a set of 1000 rows, I want randomly > pickup 100 of 1000. use the LIMIT clause example SELECT * FROM test_table LIMIT 100; you can also use the OFFSET clause to skip to n row and the fetch the n desired rows example SELECT * FROM test_table LIMIT 100 OFFSET 100; this will skip to row number 100 and the fetch the next 100 rows []�s --------------------------------------------- Joao Pedro M. F. Monoo Infortrade Information Systems #183816 Linux Registered User Slackware 7.1 running 2.4.2 Linux Kernel
Jie, > How I can return random N rows from my select stmt? > like: > e.g. what my selectee is a set of 1000 rows, I want randomly > pickup 100 of 1000. You'd have to do it inside a function or external program, and copy the rows to a temporary table (which is what you'd return to the user). Thus, language-agnostic rules: CREATE FUNCTION return_random(X) LOOP X Times totalrecs = COUNT(*) FROM maintable WHERE NOT EXISTS temptable offset_count = RANDOM*totalrecs INSERT INTO temptable ( a, b, c, d ) SELECT a, b, c, d FROM maintable LIMIT 1 OFFSET offset_count; END LOOP END; than: SELECT temptable -Josh -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Thu, 26 Apr 2001, Jie Liang wrote: > > How I can return random N rows from my select stmt? > like: > e.g. what my selectee is a set of 1000 rows, I want randomly > pickup 100 of 1000. Interesting problem. You might get much better responses than this, but, two ideas that might be workable: * use a WHERE clause that checks random() > .88 . This should give you, on average, about 120 rows out of 1000, and you can add LIMIT 100 to ensure that you get only 100. But you're still biased toward the start of the list. (Or, remove the LIMIT 100, use > .9, but there's no guarantee you'll get 100-- you'll get more or less than that. * have a plpgsql routine that gets 100 random records, and copy these into a temporary table (since plpgsql can't return a recordset.) Query against this table. Or, when all else fails: * do it in your front end (Python/Perl/PHP/Pwhatever). If you get better ideas, and they aren't cc'd to the list, please do so. HTH, -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Jie Liang <jliang@ipinc.com> wrote:
>e.g. what my selectee is a set of 1000 rows, I want randomly pickup 100 of
>1000.
Have you tried
SELECT * FROM selectee ORDER BY random() LIMIT 100;
?o
HTH,
Ray
--
<GRub[B]eR> JHM, jij bent echt nerd :))
<GRub[B]eR> maar wel een goeie :)
<GRub[B]eR> Soort van programmerende furby
Gezien op #cistron
Josh Berkus <josh@agliodbs.com> writes:
> UPDATE assignments SET status = -1
> FROM assignments JOIN orders ON assignments.order_usq = orders.usq
> WHERE orders.status = -1;
> However, I get an error of "Relation assignments referenced twice in
> query."
> Now, I can (and have) re-phrase the query so that PostgreSQL will
> accept it. However, I was under the impression that the above was
> standard SQL92. Am I mistaken?
You are mistaken. SQL92 and SQL99 don't allow a FROM clause in UPDATE
at all: they say it's just
<update statement: searched> ::= UPDATE <target table> SET <set clause list>
[ WHERE <search condition> ]
Postgres allows the clause, but treats it as supplying *additional*
table references besides the target table reference. Thus the error.
In other words: you can JOIN, but not against the target table.
regards, tom lane