Postgres Pro
Downloads
Home   >   mailing lists

Thread: update syntax

update syntax

From
"Praveen Raja"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">While using Ms SQL server I used to write update statements like this,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">UPDATE a SET a.col1 = b.col1</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size: 
10.0pt;font-family:Arial">FROM table1 a</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial">INNER JOIN table1 b</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial">ON a.col2 = b.col2</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial">WHERE a.col3 = ‘something’</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size: 
10.0pt;font-family:Arial">AND b.col3 = ‘somethingelse’</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">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?</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">/P</span></font></div>

Re: update syntax

From
Bruno Prévost
Date:
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’
 
----- Original Message -----
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 = 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

Re: update syntax

From
"Praveen Raja"
Date:

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évost [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 = b.col1
FROM table1 b
WHERE table1.col2 = b.col2 and
table1.col3 = ‘something’ and
b.col3 = ‘somethingelse’

 

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

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

Re: update syntax

From
Bruno Prévost
Date:
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 = b.col2
              WHERE a.col3 = ‘something’
              AND b.col3 = ‘somethingelse’)
 
----- Original Message -----
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évost [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 = b.col1
FROM table1 b
WHERE table1.col2 = b.col2 and
table1.col3 = ‘something’ and
b.col3 = ‘somethingelse’

 

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

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

Re: update syntax

From
Bruno Wolff III
Date:
On Wed, Jun 15, 2005 at 14:35:42 +0200, Praveen Raja <praveen.raja@netlight.se> 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’