Maksim Likharev wrote:
> My be I too spoiled by MS SQL Server, but does'nt
> syntax:
>
> update prod.t_results set expdate=e.termdate from
> work.termdate e, prod.t_results r where e.docid=r.docid;
> or
> update prod.t_results set expdate=e.termdate from
> work.termdate e inner join prod.t_results r on e.docid=r.docid;
>
> is standard SQL-92 update FROM form?
> just trying to understand.
13.10 <update statement: searched>
Function
Update rows of a table.
Format
<update statement: searched> ::=
UPDATE <table name>
SET <set clause list>
[ WHERE <search condition> ]
So, for SQL92:
UPDATE prod.t_results
SET expdate = (
SELECT e.termdate
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
);
If a 'termdate.docid' does not necessarily exist for every
't_results.docid' then you must further qualify the update to ensure
expdate won't be set to NULL (or die trying):
UPDATE prod.t_results
SET expdate = (
SELECT e.termdate
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
)
WHERE EXISTS (
SELECT 1
FROM work.termdate e
WHERE e.docid = prod.t_results.docid
);
That's SQL92 and it's ugly. I prefer the PostgreSQL extended form:
UPDATE prod.t_results
SET expdate = work.termdate.termdate
WHERE prod.t_results.docid = work.termdate.docid;
Hope that helps,
Mike Mascari
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
