Обсуждение: Simple SQL-syntax

----- 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

<p><font size="2">-----BEGIN PGP SIGNED MESSAGE-----</font><br /><font size="2">Hash: SHA1</font><p><font
size="2">Probablysomething like:</font><br /><font size="2"> </font><br /><font size="2">UPDATE tableA SET tableA.nrA =
(SELECTB.nrB FROM tableB B WHERE</font><br /><font size="2">tableA.nrA = B.nrA);</font><br /><font size="2"> </font><br
/><fontsize="2">Let me know...</font><br /><font size="2"> </font><br /><font size="2"> </font><br /><font
size="2">MikeA</font><br/><font size="2"> </font><br /><font size="2"> </font><p><font size="2">- -----Original
Message-----</font><br/><font size="2">From: Fredrik Thunberg [<a
href="mailto:fredrik@datessa.se">mailto:fredrik@datessa.se</a>]</font><br/><font size="2">Sent: 20 August 2001
12:16</font><br/><font size="2">To: pgsql-sql@postgresql.org</font><br /><font size="2">Subject: [SQL] Simple
SQL-syntax</font><br/><p><font size="2">Hello.</font><br /><font size="2"> </font><br /><font size="2">I have a very
simplesql-question from an sql-beginner:</font><br /><font size="2"> </font><br /><font size="2">Suppose I have Table A
lookingsomething like this:</font><br /><font size="2"> </font><br /><font size="2">Index    Text    NrA</font><br
/><fontsize="2"> </font><br /><font size="2">And Table B like this:</font><br /><font size="2"> </font><br /><font
size="2">NrA   NrB</font><br /><font size="2"> </font><br /><font size="2">Then I want to change all occurences of NrA
inTable A to NrB...</font><br /><font size="2"> </font><br /><font size="2">I've tried:</font><br /><font
size="2"> </font><br/><font size="2">UPDATE tableA Set tableA.nrA = B.nrB From tableB B Where tableA.nrA =</font><br
/><fontsize="2">B.nrA;</font><br /><font size="2"> </font><br /><font size="2">But that doesn't seem to
work...</font><br/><font size="2"> </font><br /><font size="2">Best regards</font><br /><font size="2"> </font><br
/><fontsize="2">/Fredrik Thunberg</font><br /><p><font size="2">-----BEGIN PGP SIGNATURE-----</font><br /><font
size="2">Version:PGPfreeware 6.5.3 for non-commercial use <<a href="http://www.pgp.com"
target="_blank">http://www.pgp.com</a>></font><p><font
size="2">iQA/AwUBO4EtanympNV/C086EQKOMwCbBLcLCUJYK5gWvOROp2IbV0+IkC4An1gN</font><br/><font
size="2">0Bys7MmGO9C6S6o8nt1YPwRQ</font><br/><font size="2">=Vhvj</font><br /><font size="2">-----END PGP
SIGNATURE-----</font><code><fontsize="3"><br /><br />
_________________________________________________________________________<br/> This e-mail and any attachments are
confidentialand may also be privileged and/or copyright <br /> material of Intec Telecom Systems PLC (or its affiliated
companies).If you are not an <br /> intended or authorised recipient of this e-mail or have received it in error,
pleasedelete <br /> it immediately and notify the sender by e-mail. In such a case, reading, reproducing, <br />
printingor further dissemination of this e-mail is strictly prohibited and may be unlawful. <br /> Intec Telecom
SystemsPLC. does not represent or warrant that an attachment hereto is free <br /> from computer viruses or other
defects.The opinions expressed in this e-mail and any <br /> attachments may be those of the author and are not
necessarilythose of Intec Telecom <br /> Systems PLC. <br /><br /> This footnote also confirms that this email message
hasbeen swept by<br /> MIMEsweeper for the presence of computer viruses. <br />
__________________________________________________________________________<br/></font></code> 

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

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

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Folks,

Ooops!  Let me correct that example:
UPDATE invoice SET invoice_total = total_of_itemsFROM (SELECT invoice_id, sum(item_amount) AS total_of_items       FROM
invoice_items)iitWHERE iit.invoice_id = invoices.id;
 

Sorry to lead people astray.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco