From fredrik@datessa.se Mon Aug 20 11:15:37 2001 Received: from chrome.datessa.se (chrome.datessa.se [192.121.160.17]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7KBFaP76608 for ; Mon, 20 Aug 2001 07:15:37 -0400 (EDT) (envelope-from fredrik@datessa.se) Received: from ctunis (c-tunis.intern.datessa.se [10.0.0.113]) by chrome.datessa.se (8.9.3/8.9.3/Debian 8.9.3-21) with SMTP id NAA17274 for ; Mon, 20 Aug 2001 13:15:33 +0200 X-Authentication-Warning: chrome.datessa.se: Host c-tunis.intern.datessa.se [10.0.0.113] claimed to be ctunis Message-ID: <01a501c12969$6e397a10$7100000a@intern.datessa.se> From: "Fredrik Thunberg" To: Subject: Simple SQL-syntax Date: Mon, 20 Aug 2001 13:15:33 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01A2_01C1297A.31B18130" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200108/239 X-Sequence-Number: 4471 This is a multi-part message in MIME format. ------=_NextPart_000_01A2_01C1297A.31B18130 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 =3D B.nrB From tableB B Where tableA.nrA =3D B= .nrA; But that doesn't seem to work... Best regards /Fredrik Thunberg ------=_NextPart_000_01A2_01C1297A.31B18130 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello.
 
I have a very simple sql-question from&nbs= p;an=20 sql-beginner:
 
Suppose I have Table A looking something l= ike=20 this:
 
Index    Text  &n= bsp;=20 NrA
 
And Table B like this:
 
NrA    NrB
 
Then I want to change all occurences of Nr= A in=20 Table A to NrB...
 
I've tried:
 
UPDATE tableA Set tableA.nrA =3D B.nr= B From=20 tableB B Where tableA.nrA =3D B.nrA;
 
But that doesn't seem to work...
 
Best regards
 
/Fredrik Thunberg
------=_NextPart_000_01A2_01C1297A.31B18130-- From jeckermann@verio.net Mon Aug 20 14:29:29 2001 Received: from akirason.se.verio.net (akirason.se.verio.net [204.27.64.122]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7KETSP36363 for ; Mon, 20 Aug 2001 10:29:29 -0400 (EDT) (envelope-from jeckermann@verio.net) Received: from beancounter (no-wkstns.gs.verio.net [204.27.67.225]) (authenticated) by akirason.se.verio.net (8.11.4/8.11.4) with ESMTP id f7KERhI40159; Mon, 20 Aug 2001 09:27:43 -0500 (CDT) (envelope-from jeckermann@verio.net) Message-ID: <01af01c12984$80c48830$279c10ac@INTERNAL> From: "Jeff Eckermann" To: "Fredrik Thunberg" , References: <01a501c12969$6e397a10$7100000a@intern.datessa.se> Subject: Re: Simple SQL-syntax Date: Mon, 20 Aug 2001 09:29:21 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01AC_01C1295A.97B45D60" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200108/245 X-Sequence-Number: 4480 This is a multi-part message in MIME format. ------=_NextPart_000_01AC_01C1295A.97B45D60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable UPDATE TableA SET nrA =3D TableB.nrB WHERE nrA =3D TableB.nrA; UPDATE statements don't take FROM clauses. I don't think they like aliasin= g, either. ----- Original Message -----=20 From: Fredrik Thunberg=20 To: pgsql-sql@postgresql.org=20 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 =3D B.nrB From tableB B Where tableA.nrA =3D= B.nrA; But that doesn't seem to work... Best regards /Fredrik Thunberg ------=_NextPart_000_01AC_01C1295A.97B45D60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
UPDATE TableA SET nrA =3D TableB.nrB WHERE= nrA =3D=20 TableB.nrA;
 
UPDATE statements don't take FROM=20 clauses.  I don't think they like aliasing, either.
----- Original Message -----
Fro= m:=20 Fredrik= =20 Thunberg
To: pgsql-sql@postgresql.org
Sent: Monday, August 20, 2001 6:15= =20 AM
Subject: Simple SQL-syntax

Hello.
 
I have a very simple sql-question from&n= bsp;an=20 sql-beginner:
 
Suppose I have Table A looking something= like=20 this:
 
Index    Text  =  =20 NrA
 
And Table B like this:
 
NrA    NrB
 
Then I want to change all occurences of = NrA in=20 Table A to NrB...
 
I've tried:
 
UPDATE tableA Set tableA.nrA =3D B.= nrB From=20 tableB B Where tableA.nrA =3D B.nrA;
 
But that doesn't seem to work...<= /DIV>
 
Best regards
 
/Fredrik=20 Thunberg
------=_NextPart_000_01AC_01C1295A.97B45D60-- From webmaster@robbyslaughter.com Mon Aug 20 14:53:23 2001 Received: from mail.utexas.edu (wb1-a.mail.utexas.edu [128.83.126.134]) by postgresql.org (8.11.3/8.11.4) with SMTP id f7KErNP43934 for ; Mon, 20 Aug 2001 10:53:23 -0400 (EDT) (envelope-from webmaster@robbyslaughter.com) Received: (qmail 6611 invoked by uid 0); 20 Aug 2001 14:53:18 -0000 Received: from adsl-64-218-161-124.dsl.austtx.swbell.net (HELO robmanbox) (64.218.161.124) by umbs-smtp-1 with SMTP; 20 Aug 2001 14:53:18 -0000 Reply-To: From: "Robby Slaughter" To: "Fredrik Thunberg" , Subject: RE: Simple SQL-syntax Date: Mon, 20 Aug 2001 09:55:47 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal In-Reply-To: <01a501c12969$6e397a10$7100000a@intern.datessa.se> X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200108/249 X-Sequence-Number: 4487 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 From josh@agliodbs.com Mon Aug 20 15:13:10 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7KFDAP51828 for ; Mon, 20 Aug 2001 11:13:10 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 105060; Mon, 20 Aug 2001 08:13:05 -0700 From: "Josh Berkus" Subject: Re: Simple SQL-syntax To: "Fredrik Thunberg" , X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 20 Aug 2001 08:13:05 -0700 Message-ID: In-Reply-To: <01a501c12969$6e397a10$7100000a@intern.datessa.se> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===105060====davinci.ethosmedia.com===_" X-Archive-Number: 200108/247 X-Sequence-Number: 4486 This is a multi-part MIME message --_===105060====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit 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 --_===105060====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===105060====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===105060====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===105060====davinci.ethosmedia.com===_-- From Michael.Ansley@intec-telecom-systems.com Mon Aug 20 15:32:31 2001 Received: from fileserver002.intecsystems.co.uk ([194.200.14.125]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7KFWVP59729 for ; Mon, 20 Aug 2001 11:32:31 -0400 (EDT) (envelope-from Michael.Ansley@intec-telecom-systems.com) Received: from fileserver002.intecsystems.co.uk (unverified) by fileserver002.intecsystems.co.uk (Content Technologies SMTPRS 4.1.5) with ESMTP id ; Mon, 20 Aug 2001 16:33:01 +0100 Received: by fileserver002.intecsystems.co.uk with Internet Mail Service (5.5.2650.21) id ; Mon, 20 Aug 2001 16:33:01 +0100 Message-ID: <7F124BC48D56D411812500D0B747251480F67E@fileserver002.intecsystems.co.uk> From: "Michael Ansley (UK)" To: "'Fredrik Thunberg'" , pgsql-sql@postgresql.org Subject: RE: Simple SQL-syntax Date: Mon, 20 Aug 2001 16:33:00 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative ; boundary="----_=_NextPart_001_01C1298D.6548E28C" X-Archive-Number: 200108/246 X-Sequence-Number: 4485 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C1298D.6548E28C Content-Type: text/plain; charset="iso-8859-1" -----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 iQA/AwUBO4EtanympNV/C086EQKOMwCbBLcLCUJYK5gWvOROp2IbV0+IkC4An1gN 0Bys7MmGO9C6S6o8nt1YPwRQ =Vhvj -----END PGP SIGNATURE----- ------_=_NextPart_001_01C1298D.6548E28C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] Simple SQL-syntax

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably something like:
 
UPDATE tableA SET tableA.nrA =3D (SELECT B.nrB FROM tabl= eB B WHERE
tableA.nrA =3D 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:<= /FONT>
 
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 t= o NrB...
 
I've tried:
 
UPDATE tableA Set tableA.nrA =3D B.nrB From tableB B Whe= re tableA.nrA =3D
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+Ik= C4An1gN
0Bys7MmGO9C6S6o8nt1YPwRQ
=3DVhvj
-----END PGP SIGNATURE-----



_________________________________________________________________________ This e-mail and any attachments are confidential and may also be privileged= and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If yo= u are not an
intended or authorised recipient of this e-mail or have received it in erro= r, please delete
it immediately and notify the sender by e-mail. In such a case, reading, r= eproducing,
printing or further dissemination of this e-mail is strictly prohibited and= may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment= hereto is free
from computer viruses or other defects. The opinions expressed in this e-ma= il and any
attachments may be those of the author and are not necessarily those of Int= ec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________<= BR>
------_=_NextPart_001_01C1298D.6548E28C-- From josh@agliodbs.com Mon Aug 20 20:21:29 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7KKLSP85408 for ; Mon, 20 Aug 2001 16:21:29 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 105344; Mon, 20 Aug 2001 13:21:25 -0700 From: "Josh Berkus" Subject: Re: Simple SQL-syntax To: , "Fredrik Thunberg" , X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 20 Aug 2001 13:21:25 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200108/254 X-Sequence-Number: 4494 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 From josh@agliodbs.com Mon Aug 20 22:06:49 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7KM6mP30693 for ; Mon, 20 Aug 2001 18:06:49 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 105447; Mon, 20 Aug 2001 15:06:45 -0700 From: "Josh Berkus" Subject: Re: Simple SQL-syntax To: Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 20 Aug 2001 15:06:45 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200108/253 X-Sequence-Number: 4500 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 From josh@agliodbs.com Mon Aug 20 23:44:59 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7KNixP66912 for ; Mon, 20 Aug 2001 19:44:59 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 105527; Mon, 20 Aug 2001 16:44:55 -0700 From: "Josh Berkus" Subject: Re: Simple SQL-syntax To: Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 20 Aug 2001 16:44:55 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200108/255 X-Sequence-Number: 4502 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