pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

update syntax
5+ messages / 3 participants
[nested] [flat]

* update syntax
@ 2005-06-15 11:39 Praveen Raja <praveen.raja@netlight.se>
  2005-06-15 12:22 ` Re: update syntax Bruno Prévost <bp@interaction.ws>
  0 siblings, 1 reply; 5+ messages in thread

From: Praveen Raja @ 2005-06-15 11:39 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: update syntax
  2005-06-15 11:39 update syntax Praveen Raja <praveen.raja@netlight.se>
@ 2005-06-15 12:22 ` Bruno Prévost <bp@interaction.ws>
  2005-06-15 12:35   ` Re: update syntax Praveen Raja <praveen.raja@netlight.se>
  0 siblings, 1 reply; 5+ messages in thread

From: Bruno Prévost @ 2005-06-15 12:22 UTC (permalink / raw)
  To: Praveen Raja <praveen.raja@netlight.se>; +Cc: pgsql-sql

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 ----- 
  From: Praveen Raja 
  To: 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 = 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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: update syntax
  2005-06-15 11:39 update syntax Praveen Raja <praveen.raja@netlight.se>
  2005-06-15 12:22 ` Re: update syntax Bruno Prévost <bp@interaction.ws>
@ 2005-06-15 12:35   ` Praveen Raja <praveen.raja@netlight.se>
  2005-06-15 12:49     ` Re: update syntax Bruno Prévost <bp@interaction.ws>
  2005-06-15 14:34     ` Re: update syntax Bruno Wolff III <bruno@wolff.to>
  0 siblings, 2 replies; 5+ messages in thread

From: Praveen Raja @ 2005-06-15 12:35 UTC (permalink / raw)
  To: 'Bruno Prévost' <bp@interaction.ws>; +Cc: pgsql-sql

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

From: Praveen Raja <mailto:praveen.raja@netlight.se>  

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: update syntax
  2005-06-15 11:39 update syntax Praveen Raja <praveen.raja@netlight.se>
  2005-06-15 12:22 ` Re: update syntax Bruno Prévost <bp@interaction.ws>
  2005-06-15 12:35   ` Re: update syntax Praveen Raja <praveen.raja@netlight.se>
@ 2005-06-15 12:49     ` Bruno Prévost <bp@interaction.ws>
  1 sibling, 0 replies; 5+ messages in thread

From: Bruno Prévost @ 2005-06-15 12:49 UTC (permalink / raw)
  To: Praveen Raja <praveen.raja@netlight.se>; +Cc: pgsql-sql

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 ----- 
  From: Praveen Raja 
  To: 'Bruno Prévost' 
  Cc: pgsql-sql@postgresql.org 
  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 ----- 

    From: Praveen Raja 

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: update syntax
  2005-06-15 11:39 update syntax Praveen Raja <praveen.raja@netlight.se>
  2005-06-15 12:22 ` Re: update syntax Bruno Prévost <bp@interaction.ws>
  2005-06-15 12:35   ` Re: update syntax Praveen Raja <praveen.raja@netlight.se>
@ 2005-06-15 14:34     ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 5+ messages in thread

From: Bruno Wolff III @ 2005-06-15 14:34 UTC (permalink / raw)
  To: Praveen Raja <praveen.raja@netlight.se>; +Cc: 'Bruno Prévost' <bp@interaction.ws>; pgsql-sql

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’



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

end of thread, other threads:[~2005-06-15 14:34 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-15 11:39 update syntax Praveen Raja <praveen.raja@netlight.se>
2005-06-15 12:22 ` Bruno Prévost <bp@interaction.ws>
2005-06-15 12:35   ` Praveen Raja <praveen.raja@netlight.se>
2005-06-15 12:49     ` Bruno Prévost <bp@interaction.ws>
2005-06-15 14:34     ` Bruno Wolff III <bruno@wolff.to>

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