pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple SQL-syntax
8+ messages / 5 participants
[nested] [flat]
* Simple SQL-syntax
@ 2001-08-20 11:15 Fredrik Thunberg <fredrik@datessa.se>
0 siblings, 3 replies; 8+ messages in thread
From: Fredrik Thunberg @ 2001-08-20 11:15 UTC (permalink / raw)
To: pgsql-sql
Hello.
I have a very simple sql-question from an sql-beginner:
Suppose I have Table A looking something like this:
Index Text NrA
And Table B like this:
NrA NrB
Then I want to change all occurences of NrA in Table A to NrB...
I've tried:
UPDATE tableA Set tableA.nrA = B.nrB From tableB B Where tableA.nrA = B.nrA;
But that doesn't seem to work...
Best regards
/Fredrik Thunberg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Simple SQL-syntax
@ 2001-08-20 14:29 Jeff Eckermann <jeckermann@verio.net>
parent: Fredrik Thunberg <fredrik@datessa.se>
2 siblings, 0 replies; 8+ messages in thread
From: Jeff Eckermann @ 2001-08-20 14:29 UTC (permalink / raw)
To: Fredrik Thunberg <fredrik@datessa.se>; pgsql-sql
UPDATE TableA SET nrA = TableB.nrB WHERE nrA = TableB.nrA;
UPDATE statements don't take FROM clauses. I don't think they like aliasing, either.
----- Original Message -----
From: Fredrik Thunberg
To: pgsql-sql@postgresql.org
Sent: Monday, August 20, 2001 6:15 AM
Subject: Simple SQL-syntax
Hello.
I have a very simple sql-question from an sql-beginner:
Suppose I have Table A looking something like this:
Index Text NrA
And Table B like this:
NrA NrB
Then I want to change all occurences of NrA in Table A to NrB...
I've tried:
UPDATE tableA Set tableA.nrA = B.nrB From tableB B Where tableA.nrA = B.nrA;
But that doesn't seem to work...
Best regards
/Fredrik Thunberg
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Simple SQL-syntax
@ 2001-08-20 14:55 Robby Slaughter <webmaster@robbyslaughter.com>
parent: Fredrik Thunberg <fredrik@datessa.se>
2 siblings, 1 reply; 8+ messages in thread
From: Robby Slaughter @ 2001-08-20 14:55 UTC (permalink / raw)
To: Fredrik Thunberg <fredrik@datessa.se>; pgsql-sql
Fredrik,
Just for future reference, it's generally a good idea to include a complete
table schema
and some sample data for your tables when asking this kind of question. But
I think I know
what you mean and will try to help you.
>Suppose I have Table A looking something like this:
>Index Text NrA
>And Table B like this:
>NrA NrB
>Then I want to change all occurences of NrA in Table A to NrB...
First of all, your syntax isn't quite right. It's
UPDATE tablename SET value = value WHERE [conditions];
There's no FROM clause in an update statement. And second, you
need to be careful to make sure all your tokens make sense. You
had an extra "B" in the middle of your statement. Based on this:
UPDATE tableA,tableB SET tableA.NrA = tableB.NrB WHERE tableA.NrA =
tableB.NrB
should achieve the desired result.
Hope this helps!
-Robby Slaughter
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Simple SQL-syntax
@ 2001-08-20 15:13 Josh Berkus <josh@agliodbs.com>
parent: Fredrik Thunberg <fredrik@datessa.se>
2 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2001-08-20 15:13 UTC (permalink / raw)
To: Fredrik Thunberg <fredrik@datessa.se>; pgsql-sql
Fredrik,
> I have a very simple sql-question from an sql-beginner:
Sure. Keep in mind that there is a pgsql-newbie list. Also quite a few
good books on the topic, including: Database Design for Mere Mortals and
SQL for Smarties.
> Then I want to change all occurences of NrA in Table A to NrB...
>
> I've tried:
>
UPDATE tableA Set tableA.nrA = B.nrB
From tableB B Where tableA.nrA = B.nrA;
This should work. What error message are you getting? And what version
of Postgres are you using?
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Simple SQL-syntax
@ 2001-08-20 15:33 Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 8+ messages in thread
From: Michael Ansley (UK) @ 2001-08-20 15:33 UTC (permalink / raw)
To: 'Fredrik Thunberg' <fredrik@datessa.se>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Probably something like:
UPDATE tableA SET tableA.nrA = (SELECT B.nrB FROM tableB B WHERE
tableA.nrA = B.nrA);
Let me know...
MikeA
- -----Original Message-----
From: Fredrik Thunberg [mailto:fredrik@datessa.se]
Sent: 20 August 2001 12:16
To: pgsql-sql@postgresql.org
Subject: [SQL] Simple SQL-syntax
Hello.
I have a very simple sql-question from an sql-beginner:
Suppose I have Table A looking something like this:
Index Text NrA
And Table B like this:
NrA NrB
Then I want to change all occurences of NrA in Table A to NrB...
I've tried:
UPDATE tableA Set tableA.nrA = B.nrB From tableB B Where tableA.nrA =
B.nrA;
But that doesn't seem to work...
Best regards
/Fredrik Thunberg
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com;
iQA/AwUBO4EtanympNV/C086EQKOMwCbBLcLCUJYK5gWvOROp2IbV0+IkC4An1gN
0Bys7MmGO9C6S6o8nt1YPwRQ
=Vhvj
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Simple SQL-syntax
@ 2001-08-20 20:21 Josh Berkus <josh@agliodbs.com>
parent: Robby Slaughter <webmaster@robbyslaughter.com>
0 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2001-08-20 20:21 UTC (permalink / raw)
To: webmaster@robbyslaughter.com; Fredrik Thunberg <fredrik@datessa.se>; pgsql-sql
Robby,
> There's no FROM clause in an update statement. And second, you
> need to be careful to make sure all your tokens make sense. You
> had an extra "B" in the middle of your statement. Based on this:
Actually, Postgres *does* support UPDATE ... FROM. I use it quite a
bit. The syntax can be fussy, however.
The following is perfectly valid in Postgres, although not 100% ANSI-92
kosher:
UPDATE A SET 1 = B.2
FROM B
WHERE A.2 = B.2
Fredrik's problem may be simply the table reference after SET; that's
why I'd like to see his exact parse error message.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Simple SQL-syntax
@ 2001-08-20 22:06 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2001-08-20 22:06 UTC (permalink / raw)
To: webmaster@robbyslaughter.com; +Cc: pgsql-sql
Robby,
> Do you know if, internally there's any difference between
>
> UPDATE A,B SET a.f1 = b.f1 WHERE a.i = b.i;
>
> -and-
>
> UPDATE A SET a.f1 = b.f2 FROM B WHERE a.i = b.i;
>
> Just wondering why the SQL standard would be broken in this
> case---and if there's any reason to learn this particular
> aspect of Postgres functionality....
Only if you want to use JOINS, sub-selects, and/or aliasing in your
UPDATE statement. For example, I have a table that caches subtotals of
its detail-level subtable:
UPDATE invoice SET invoice_total = total_of_items
FROM (SELECT invoice_id, sum(item_amount) FROM invoice_items) iit
WHERE iit.invoice_id = invoices.id;
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Simple SQL-syntax
@ 2001-08-20 23:44 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2001-08-20 23:44 UTC (permalink / raw)
To: webmaster@robbyslaughter.com; +Cc: pgsql-sql
Folks,
Ooops! Let me correct that example:
UPDATE invoice SET invoice_total = total_of_items
FROM (SELECT invoice_id, sum(item_amount) AS total_of_items
FROM invoice_items) iit
WHERE iit.invoice_id = invoices.id;
Sorry to lead people astray.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-08-20 23:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-20 11:15 Simple SQL-syntax Fredrik Thunberg <fredrik@datessa.se>
2001-08-20 14:29 ` Jeff Eckermann <jeckermann@verio.net>
2001-08-20 14:55 ` Robby Slaughter <webmaster@robbyslaughter.com>
2001-08-20 20:21 ` Josh Berkus <josh@agliodbs.com>
2001-08-20 15:13 ` Josh Berkus <josh@agliodbs.com>
2001-08-20 15:33 RE: Simple SQL-syntax Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>
2001-08-20 22:06 Re: Simple SQL-syntax Josh Berkus <josh@agliodbs.com>
2001-08-20 23:44 ` Josh Berkus <josh@agliodbs.com>
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