pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing 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>
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 09:13 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Christian Rudow <Christian.Rudow@thinx.ch>
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 14:37 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
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