pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate syntax
5+ messages / 3 participants
[nested] [flat]
* update syntax
@ 2005-06-15 11:39 Praveen Raja <praveen.raja@netlight.se>
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 12:22 Bruno Prévost <bp@interaction.ws>
parent: 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 12:35 Praveen Raja <praveen.raja@netlight.se>
parent: Bruno Prévost <bp@interaction.ws>
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 12:49 Bruno Prévost <bp@interaction.ws>
parent: Praveen Raja <praveen.raja@netlight.se>
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 14:34 Bruno Wolff III <bruno@wolff.to>
parent: Praveen Raja <praveen.raja@netlight.se>
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