Обсуждение: 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