pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Simple SQL-syntax
8+ messages / 5 participants
[nested] [flat]

* Simple SQL-syntax
@ 2001-08-20 11:15 Fredrik Thunberg <fredrik@datessa.se>
  2001-08-20 14:29 ` Re: Simple SQL-syntax Jeff Eckermann <jeckermann@verio.net>
  2001-08-20 14:55 ` RE: Simple SQL-syntax Robby Slaughter <webmaster@robbyslaughter.com>
  2001-08-20 15:13 ` Re: Simple SQL-syntax Josh Berkus <josh@agliodbs.com>
  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 11:15 Simple SQL-syntax Fredrik Thunberg <fredrik@datessa.se>
@ 2001-08-20 14:29 ` Jeff Eckermann <jeckermann@verio.net>
  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 11:15 Simple SQL-syntax Fredrik Thunberg <fredrik@datessa.se>
@ 2001-08-20 14:55 ` Robby Slaughter <webmaster@robbyslaughter.com>
  2001-08-20 20:21   ` Re: Simple SQL-syntax Josh Berkus <josh@agliodbs.com>
  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 11:15 Simple SQL-syntax Fredrik Thunberg <fredrik@datessa.se>
  2001-08-20 14:55 ` RE: Simple SQL-syntax Robby Slaughter <webmaster@robbyslaughter.com>
@ 2001-08-20 20:21   ` Josh Berkus <josh@agliodbs.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 11:15 Simple SQL-syntax Fredrik Thunberg <fredrik@datessa.se>
@ 2001-08-20 15:13 ` Josh Berkus <josh@agliodbs.com>
  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 22:06 Josh Berkus <josh@agliodbs.com>
  2001-08-20 23:44 ` Re: Simple SQL-syntax 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 22:06 Re: Simple SQL-syntax Josh Berkus <josh@agliodbs.com>
@ 2001-08-20 23:44 ` 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