From praveen.raja@netlight.se Wed Jun 15 11:39:06 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BBBE95289D for ; Wed, 15 Jun 2005 08:39:06 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69280-08 for ; Wed, 15 Jun 2005 11:38:58 +0000 (GMT) Received: from mail.sto.netlight.se (1-1-10-39a.sh.sth.bostream.se [82.182.50.216]) by svr1.postgresql.org (Postfix) with ESMTP id 2A81D52836 for ; Wed, 15 Jun 2005 08:38:56 -0300 (ADT) Received: from prraxp ([192.168.12.76]) by mail.sto.netlight.se (8.11.2/8.11.2) with ESMTP id j5FCRu316389 for ; Wed, 15 Jun 2005 14:27:57 +0200 From: "Praveen Raja" To: Subject: update syntax Date: Wed, 15 Jun 2005 13:39:11 +0200 Message-ID: <002401c5719e$dc9682b0$4c0ca8c0@sek.se> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0025_01C571AF.A01F52B0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4024 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.065 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/166 X-Sequence-Number: 21767 This is a multi-part message in MIME format. ------=_NextPart_000_0025_01C571AF.A01F52B0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi, While using Ms SQL server I used to write update statements like this, UPDATE a SET a.col1 = b.col1 FROM table1 a INNER JOIN table1 b ON a.col2 = b.col2 WHERE a.col3 = 'something' AND b.col3 = 'somethingelse' But I can't seem to do this in postgres, it gives me an error saying 'syntax error at or near "."'. How can I do something similar to this i.e. join on the same table? Thanks, /P ------=_NextPart_000_0025_01C571AF.A01F52B0 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi,

While using Ms SQL server I used to write = update statements like this,

UPDATE a SET a.col1 =3D = b.col1

FROM table1 a

INNER JOIN table1 b

ON a.col2 =3D b.col2

WHERE a.col3 =3D = ‘something’

AND b.col3 =3D = ‘somethingelse’

But I can’t seem to do this in postgres, = it gives me an error saying ‘syntax error at or near = “.”’. How can I do something similar to this i.e. join on the same = table?

Thanks,

/P

------=_NextPart_000_0025_01C571AF.A01F52B0-- From bp@interaction.ws Wed Jun 15 12:22:10 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 61EC352818 for ; Wed, 15 Jun 2005 09:22:10 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82861-04 for ; Wed, 15 Jun 2005 12:22:00 +0000 (GMT) Received: from mail.interaction.ws (unknown [216.46.25.69]) by svr1.postgresql.org (Postfix) with ESMTP id EB2F452828 for ; Wed, 15 Jun 2005 09:21:58 -0300 (ADT) Received: from [192.168.0.93] (helo=BRUNO2500) by mail.interaction.ws with smtp (Exim 4.50) id 1DiWuA-0007uu-2D; Wed, 15 Jun 2005 08:22:02 -0400 Message-ID: <006b01c571a4$dbbb7d40$5d00a8c0@BRUNO2500> From: =?iso-8859-1?Q?Bruno_Pr=E9vost?= To: "Praveen Raja" Cc: References: <002401c5719e$dc9682b0$4c0ca8c0@sek.se> Subject: Re: update syntax Date: Wed, 15 Jun 2005 08:22:11 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0068_01C57183.53FE33F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/168 X-Sequence-Number: 21769 This is a multi-part message in MIME format. ------=_NextPart_000_0068_01C57183.53FE33F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Try something like this UPDATE table1 SET col1 =3D b.col1 FROM table1 b WHERE table1.col2 =3D b.col2 and table1.col3 =3D 'something' and b.col3 =3D 'somethingelse' ----- Original Message -----=20 From: Praveen Raja=20 To: pgsql-sql@postgresql.org=20 Sent: Wednesday, June 15, 2005 7:39 AM Subject: [SQL] update syntax Hi, While using Ms SQL server I used to write update statements like this, UPDATE a SET a.col1 =3D b.col1 FROM table1 a INNER JOIN table1 b ON a.col2 =3D b.col2 WHERE a.col3 =3D 'something' AND b.col3 =3D 'somethingelse' But I can't seem to do this in postgres, it gives me an error saying = 'syntax error at or near "."'. How can I do something similar to this = i.e. join on the same table? Thanks, /P ------=_NextPart_000_0068_01C57183.53FE33F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Try something like this

UPDATE table1
SET col1 =3D = b.col1
FROM table1=20 b
WHERE table1.col2 =3D b.col2 and
table1.col3 =3D =91something=92 = and
b.col3 =3D=20 =91somethingelse=92

----- Original Message -----

From:=20 Praveen Raja
To: pgsql-sql@postgresql.org =

Sent: Wednesday, June 15, 2005 = 7:39=20 AM

Subject: [SQL] update = syntax

Hi,

While using Ms SQL = server I used=20 to write update statements like this,

UPDATE a SET a.col1 =3D=20 b.col1

FROM table1 = a

INNER JOIN table1=20 b

ON a.col2 =3D=20 b.col2

WHERE a.col3 =3D=20 =91something=92

AND b.col3 =3D=20 =91somethingelse=92

But I can=92t seem to do = this in=20 postgres, it gives me an error saying =91syntax error at or near = =93.=94=92. How can I=20 do something similar to this i.e. join on the same = table?

Thanks,

/P

------=_NextPart_000_0068_01C57183.53FE33F0-- From praveen.raja@netlight.se Wed Jun 15 12:35:45 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 161DF5282B for ; Wed, 15 Jun 2005 09:35:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85170-06 for ; Wed, 15 Jun 2005 12:35:35 +0000 (GMT) Received: from mail.sto.netlight.se (1-1-10-39a.sh.sth.bostream.se [82.182.50.216]) by svr1.postgresql.org (Postfix) with ESMTP id BC12C52818 for ; Wed, 15 Jun 2005 09:35:32 -0300 (ADT) Received: from prraxp ([192.168.12.76]) by mail.sto.netlight.se (8.11.2/8.11.2) with ESMTP id j5FDOR318792; Wed, 15 Jun 2005 15:24:30 +0200 From: "Praveen Raja" To: "=?iso-8859-1?Q?'Bruno_Pr=E9vost'?=" Cc: Subject: Re: update syntax Date: Wed, 15 Jun 2005 14:35:42 +0200 Message-ID: <003001c571a6$c337a1c0$4c0ca8c0@sek.se> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0031_01C571B7.86C071C0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4024 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: <006b01c571a4$dbbb7d40$5d00a8c0@BRUNO2500> Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.058 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/169 X-Sequence-Number: 21770 This is a multi-part message in MIME format. ------=_NextPart_000_0031_01C571B7.86C071C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks. The UPDATE works ok now. But using the same logic it doesn=92t seem possible to delete rows. Is this also possible? =20 /P =20 -----Original Message----- From: Bruno Pr=E9vost [mailto:bp@interaction.ws]=20 Sent: 15 June 2005 14:22 To: Praveen Raja Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] update syntax =20 Try something like this =20 UPDATE table1 SET col1 =3D b.col1 FROM table1 b WHERE table1.col2 =3D b.col2 and table1.col3 =3D =91something=92 and b.col3 =3D =91somethingelse=92 =20 ----- Original Message -----=20 From: Praveen Raja =20 To: pgsql-sql@postgresql.org=20 Sent: Wednesday, June 15, 2005 7:39 AM Subject: [SQL] update syntax =20 Hi, =20 While using Ms SQL server I used to write update statements like this, =20 UPDATE a SET a.col1 =3D b.col1 FROM table1 a INNER JOIN table1 b ON a.col2 =3D b.col2 WHERE a.col3 =3D =91something=92 AND b.col3 =3D =91somethingelse=92 =20 But I can=92t seem to do this in postgres, it gives me an error saying =91syntax error at or near =93.=94=92. How can I do something similar to = this i.e. join on the same table? =20 Thanks, /P ------=_NextPart_000_0031_01C571B7.86C071C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Thanks. The = UPDATE works ok now. But using the same logic it doesn’t seem possible to = delete rows. Is this also possible?

/P<= /p>

-----Original Message-----
From: Bruno Pr=E9vost [mailto:bp@interaction.ws]
Sent: 15 June 2005 = 14:22
To: Praveen Raja
Cc: = pgsql-sql@postgresql.org
Subject: Re: [SQL] update = syntax

Try something like = this

UPDATE table1
SET col1 =3D b.col1
FROM table1 b
WHERE table1.col2 =3D b.col2 and
table1.col3 =3D ‘something’ and
b.col3 =3D ‘somethingelse’

----- Original = Message -----

From: Praveen Raja

To:<= /font> pgsql-sql@postgresql.org

Sent: Wednesday, June 15, 2005 7:39 AM

Subject: [SQL] update syntax

Hi,

While using Ms = SQL server I used to write update statements like this,

UPDATE a SET = a.col1 =3D b.col1

FROM table1 = a

INNER JOIN = table1 b

ON a.col2 =3D = b.col2

WHERE a.col3 = =3D ‘something’

AND b.col3 =3D ‘somethingelse’

But I = can’t seem to do this in postgres, it gives me an error saying ‘syntax error at = or near “.”’. How can I do something similar to this i.e. join = on the same table?

Thanks,

/P

------=_NextPart_000_0031_01C571B7.86C071C0-- From bp@interaction.ws Wed Jun 15 12:49:47 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7DF5A5283C for ; Wed, 15 Jun 2005 09:49:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88520-03 for ; Wed, 15 Jun 2005 12:49:39 +0000 (GMT) Received: from mail.interaction.ws (unknown [216.46.25.69]) by svr1.postgresql.org (Postfix) with ESMTP id AEF8D52818 for ; Wed, 15 Jun 2005 09:49:37 -0300 (ADT) Received: from [192.168.0.93] (helo=BRUNO2500) by mail.interaction.ws with smtp (Exim 4.50) id 1DiXKv-0007xW-SD; Wed, 15 Jun 2005 08:49:42 -0400 Message-ID: <009001c571a8$b90eb380$5d00a8c0@BRUNO2500> From: =?iso-8859-1?Q?Bruno_Pr=E9vost?= To: "Praveen Raja" Cc: References: <003001c571a6$c337a1c0$4c0ca8c0@sek.se> Subject: Re: update syntax Date: Wed, 15 Jun 2005 08:49:52 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008D_01C57187.31EEBBA0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/170 X-Sequence-Number: 21771 This is a multi-part message in MIME format. ------=_NextPart_000_008D_01C57187.31EEBBA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I don't know if you can do that using the same logic. Perhaps someone else can help? For sure, you can use a subselect. delete from table1 where col1 in (SELECT a.col1 FROM table1 a JOIN table1 b ON a.col2 =3D b.col2 WHERE a.col3 =3D 'something' AND b.col3 =3D 'somethingelse') ----- Original Message -----=20 From: Praveen Raja=20 To: 'Bruno Pr=E9vost'=20 Cc: pgsql-sql@postgresql.org=20 Sent: Wednesday, June 15, 2005 8:35 AM Subject: RE: [SQL] update syntax Thanks. The UPDATE works ok now. But using the same logic it doesn't = seem possible to delete rows. Is this also possible? /P -----Original Message----- From: Bruno Pr=E9vost [mailto:bp@interaction.ws]=20 Sent: 15 June 2005 14:22 To: Praveen Raja Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] update syntax Try something like this UPDATE table1 SET col1 =3D b.col1 FROM table1 b WHERE table1.col2 =3D b.col2 and table1.col3 =3D 'something' and b.col3 =3D 'somethingelse' ----- Original Message -----=20 From: Praveen Raja=20 To: pgsql-sql@postgresql.org=20 Sent: Wednesday, June 15, 2005 7:39 AM Subject: [SQL] update syntax Hi, While using Ms SQL server I used to write update statements like = this, UPDATE a SET a.col1 =3D b.col1 FROM table1 a INNER JOIN table1 b ON a.col2 =3D b.col2 WHERE a.col3 =3D 'something' AND b.col3 =3D 'somethingelse' But I can't seem to do this in postgres, it gives me an error saying = 'syntax error at or near "."'. How can I do something similar to this = i.e. join on the same table? Thanks, /P ------=_NextPart_000_008D_01C57187.31EEBBA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I don't know if you can do that using = the same=20 logic.

Perhaps someone else can = help?

For sure, you can use a=20 subselect.

delete from table1
where = col1 in (SELECT=20 a.col1
&nb= sp; =20 FROM table1 a JOIN table1 b ON a.col2 =3D=20 b.col2
&nb= sp; =20 WHERE a.col3 =3D =91something=92

&nbs= p; =20 AND b.col3 =3D =91somethingelse=92)

----- Original Message -----

From:=20 Praveen Raja
To: 'Bruno Pr=E9vost'

Cc: pgsql-sql@postgresql.org =

Sent: Wednesday, June 15, 2005 = 8:35=20 AM

Subject: RE: [SQL] update = syntax

Thanks. The = UPDATE=20 works ok now. But using the same logic it doesn=92t seem possible to = delete=20 rows. Is this also possible?

/P

-----Original=20 Message-----
From: = Bruno=20 Pr=E9vost [mailto:bp@interaction.ws]
Sent: 15 June 2005 = 14:22
To: Praveen Raja
Cc: pgsql-sql@postgresql.org
= Subject: Re: [SQL] update=20 syntax

Try something like=20 this

UPDATE table1
SET = col1 =3D=20 b.col1
FROM table1 b
WHERE table1.col2 =3D b.col2 = and
table1.col3 =3D=20 =91something=92 and
b.col3 =3D = =91somethingelse=92

----- Original Message -----=20

From: Praveen Raja=20

To: pgsql-sql@postgresql.org=20

Sent:=20 Wednesday, June 15, 2005 7:39 AM

Subject: [SQL]=20 update syntax

Hi,

While = using Ms SQL=20 server I used to write update statements like = this,

UPDATE a = SET a.col1 =3D=20 b.col1

FROM = table1=20 a

INNER = JOIN table1=20 b

ON a.col2 = =3D=20 b.col2

WHERE = a.col3 =3D=20 =91something=92

AND = b.col3 =3D=20 =91somethingelse=92

But I = can=92t seem to=20 do this in postgres, it gives me an error saying =91syntax error at = or near=20 =93.=94=92. How can I do something similar to this i.e. join on the = same=20 table?

Thanks,

/P

= ------=_NextPart_000_008D_01C57187.31EEBBA0-- From bruno@wolff.to Wed Jun 15 14:34:45 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1AC565283F for ; Wed, 15 Jun 2005 11:34:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14244-03 for ; Wed, 15 Jun 2005 14:34:41 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id DAEB352835 for ; Wed, 15 Jun 2005 11:34:40 -0300 (ADT) Received: (qmail 8567 invoked by uid 500); 15 Jun 2005 14:34:16 -0000 Date: Wed, 15 Jun 2005 09:34:16 -0500 From: Bruno Wolff III To: Praveen Raja Cc: 'Bruno =?iso-8859-1?Q?Pr=E9vost'?= , pgsql-sql@postgresql.org Subject: Re: update syntax Message-ID: <20050615143416.GD7595@wolff.to> Mail-Followup-To: Bruno Wolff III , Praveen Raja , 'Bruno =?iso-8859-1?Q?Pr=E9vost'?= , pgsql-sql@postgresql.org References: <006b01c571a4$dbbb7d40$5d00a8c0@BRUNO2500> <003001c571a6$c337a1c0$4c0ca8c0@sek.se> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <003001c571a6$c337a1c0$4c0ca8c0@sek.se> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/172 X-Sequence-Number: 21773 On Wed, Jun 15, 2005 at 14:35:42 +0200, Praveen Raja wrote: > Thanks. The UPDATE works ok now. But using the same logic it doesn�t > seem possible to delete rows. Is this also possible? Yes. When you use table names in the where clause they are automatically added to the join list if they aren't list in the from item list. For 8.1 this will be changing. There will be a USING clause on DELETE statements that can be used to list extra tables and the implied from feature will be disabled by default. > > Try something like this > > > > UPDATE table1 > SET col1 = b.col1 > FROM table1 b > WHERE table1.col2 = b.col2 and > table1.col3 = �something� and > b.col3 = �somethingelse�