pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
using UPDATE
4+ messages / 3 participants
[nested] [flat]

* using UPDATE
@ 1999-06-28 07:05 Christian Rudow <Christian.Rudow@thinx.ch>
  0 siblings, 0 replies; 4+ messages in thread

From: Christian Rudow @ 1999-06-28 07:05 UTC (permalink / raw)
  To: pgsql-sql

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 alternate key.

 select a.a_id from a,b
 where 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 ?

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Using Update
@ 1999-06-28 08:27 Christian Rudow <Christian.Rudow@thinx.ch>
  1999-06-28 09:13 ` Re: [SQL] Using Update Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 1 reply; 4+ messages in thread

From: Christian Rudow @ 1999-06-28 08:27 UTC (permalink / raw)
  To: pgsql-sql


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,b
 where 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Using Update
  1999-06-28 08:27 Using Update Christian Rudow <Christian.Rudow@thinx.ch>
@ 1999-06-28 09:13 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1999-06-28 14:37   ` Re: [SQL] Using Update Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
  0 siblings, 1 reply; 4+ messages in thread

From: Herouth Maoz @ 1999-06-28 09:13 UTC (permalink / raw)
  To: Christian Rudow <Christian.Rudow@thinx.ch>; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Using Update
  1999-06-28 08:27 Using Update Christian Rudow <Christian.Rudow@thinx.ch>
  1999-06-28 09:13 ` Re: [SQL] Using Update Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-06-28 14:37   ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
  0 siblings, 0 replies; 4+ messages in thread

From: Ross J. Reedstrom @ 1999-06-28 14:37 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-06-28 14:37 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-28 07:05 using UPDATE Christian Rudow <Christian.Rudow@thinx.ch>
1999-06-28 08:27 Using Update Christian Rudow <Christian.Rudow@thinx.ch>
1999-06-28 09:13 ` Re: [SQL] Using Update Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-28 14:37   ` Re: [SQL] Using Update Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox