Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using Update

Using Update

From
Christian Rudow
Date:
I have the following SQL understanding problem concerning UPDATE :

I have two tables I want to join over a new artificial key a_id.
The tables can already be joined over an existing foreign key.
select a.a_id from a,bwhere a.custno = b.custno

=> selects about 1000 id's

b.a_id so far only holds NULL values and should be updated to hold the
correct foreign key references to a.a_id.


+-------------------+
| a                 |
|-------------------|
| a_id    prim. key |0---+
| custno  altern.key|    |
+-------------------+    |                        |                       mc               +----------------+
   | b              |               +----------------+               | b_id  prim.Key |               | a_id
forgn.Key|              1 custno         |               +----------------+
 


What i want to do is something like this :
------------------------------------------
update b
set b.a_id = a.a_id
where b.custno = a.custno;


or is that the right way to do it ?  :

update b
set b.a_id = (select a.a_id             from a,b             where a.custno = b.custno)
where b.custno in (select a.custno from a);

I just seem to use the UPDATE not correctly, it cannot be parsed.
What is the correct solution to this ?
Can anyone help ?

I know that's a RTFM but I have no FM to R.
Is there a good SQL tutorial/reference accessible over the net ?

ThanX
-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Christian Rudow                 E-Mail: Christian.Rudow@thinx.ch
ThinX networked business services    Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Re: [SQL] Using Update

From
Herouth Maoz
Date:
At 11:27 +0300 on 28/06/1999, Christian Rudow wrote:


> I just seem to use the UPDATE not correctly, it cannot be parsed.
> What is the correct solution to this ?
> Can anyone help ?
>
> I know that's a RTFM but I have no FM to R.
> Is there a good SQL tutorial/reference accessible over the net ?

The usual FM is recommended. That is, the PostgreSQL user guide, which
comes with the software, if you compile it from source. Can't guarantee
that it comes with rpms. You can also man (postgres comes with manpages).
And you can also do a "\h update" in psql, to get the bare-bones syntax.

>From the manpage:

SYNOPSIS    update classname set attname-1 = expression-1         [, attname-i = expression-i]         [from from-list]
       [where qual]
 


So, it appears that the correct syntax in Postgres would be:

UPDATE b
SET b.a_id = a.aid
FROM a
WHERE b.custno = a.custno;

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Re: [SQL] Using Update

From
"Ross J. Reedstrom"
Date:
On Mon, Jun 28, 1999 at 12:13:49PM +0300, Herouth Maoz wrote:
A minor nit here. postgresql doesn't like class qualifications on the
field name tagets in an update (at least with 6.4.2), so the set line
needs to lose the "b." part, as below:

> 
> So, it appears that the correct syntax in Postgres would be:
> 
> UPDATE b
> SET b.a_id = a.aid

SET a_id=a.aid

> FROM a
> WHERE b.custno = a.custno;
> 

Ross