pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcan I update multiple table at a time?
7+ messages / 5 participants
[nested] [flat]
* can I update multiple table at a time?
@ 2008-10-10 05:12 Jagadeesh <mnjagadeesh@gmail.com>
0 siblings, 4 replies; 7+ messages in thread
From: Jagadeesh @ 2008-10-10 05:12 UTC (permalink / raw)
To: pgsql-admin
Hello Gurus,
Is it possible to update two table using single sql statement? huh?
let me be clear
I have table master with state and date field. And in history table I
have same fields i.e., state and date. I'm writing an update statement
to set 'open' state records to 'closed' state after 7. both table can
be joined using id.
Any thoughts?
Thanks
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can I update multiple table at a time?
@ 2008-10-10 05:53 Joshua Drake <jd@commandprompt.com>
parent: Jagadeesh <mnjagadeesh@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Joshua Drake @ 2008-10-10 05:53 UTC (permalink / raw)
To: Jagadeesh <mnjagadeesh@gmail.com>; +Cc: pgsql-admin
On Thu, 9 Oct 2008 22:12:04 -0700 (PDT)
Jagadeesh <mnjagadeesh@gmail.com> wrote:
> Hello Gurus,
>
> Is it possible to update two table using single sql statement? huh?
> let me be clear
>
> I have table master with state and date field. And in history table I
> have same fields i.e., state and date. I'm writing an update statement
> to set 'open' state records to 'closed' state after 7. both table can
> be joined using id.
>
> Any thoughts?
Use a transaction?
>
> Thanks
>
--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can I update multiple table at a time?
@ 2008-10-10 06:06 Julius Tuskenis <julius@nsoft.lt>
parent: Jagadeesh <mnjagadeesh@gmail.com>
3 siblings, 1 reply; 7+ messages in thread
From: Julius Tuskenis @ 2008-10-10 06:06 UTC (permalink / raw)
To: Jagadeesh <mnjagadeesh@gmail.com>; +Cc: pgsql-admin
Hi, Jagadeesh
I would suggest you 2 solutions:
1. to use a function to edit these fields. That way you will be sure
that you will not forget to update both tables.
2. use a trigger on master table. If I understand you - the history
table could be filled using trigger, when data in master table changes.
That way you could change only master table in your application.
Good luck!
Jagadeesh rašė:
> Hello Gurus,
>
> Is it possible to update two table using single sql statement? huh?
> let me be clear
>
> I have table master with state and date field. And in history table I
> have same fields i.e., state and date. I'm writing an update statement
> to set 'open' state records to 'closed' state after 7. both table can
> be joined using id.
>
> Any thoughts?
>
> Thanks
>
>
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can I update multiple table at a time?
@ 2008-10-10 07:54 Jagadeesh <mnjagadeesh@gmail.com>
parent: Julius Tuskenis <julius@nsoft.lt>
0 siblings, 1 reply; 7+ messages in thread
From: Jagadeesh @ 2008-10-10 07:54 UTC (permalink / raw)
To: pgsql-admin
Hi Julius Tuskenis,
Many thanks for helping me. These are good solutions. But I have some
doubts
Look below
On Oct 10, 11:06 am, jul...@nsoft.lt (Julius Tuskenis) wrote:
> I would suggest you 2 solutions:
> 1. to use a function to edit these fields. That way you will be sure
> that you will not forget to update both tables.
> 2. use a trigger on master table. If I understand you - the history
> table could be filled using trigger, when data in master table changes.
> That way you could change only master table in your application.
>
I think having triggers would bring performance down. So instead can
we have single
sql update statement to update all four fields of two tables? Just a
crazy idea.
> Good luck!
>
Thanks for the wish.
These wishes needed to implement smart solution to above problem :)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can I update multiple table at a time?
@ 2008-10-10 09:15 Julius Tuskenis <julius@nsoft.lt>
parent: Jagadeesh <mnjagadeesh@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Julius Tuskenis @ 2008-10-10 09:15 UTC (permalink / raw)
To: Jagadeesh <mnjagadeesh@gmail.com>; +Cc: pgsql-admin
I don't know a way to edit 2 tables in 1 SQL statement. If you have 2
sql statements then the performance should be almost the same with the
function and maybe slightly worse with a trigger.
Jagadeesh rašė:
> Hi Julius Tuskenis,
>
> Many thanks for helping me. These are good solutions. But I have some
> doubts
> Look below
>
> On Oct 10, 11:06 am, jul...@nsoft.lt (Julius Tuskenis) wrote:
>
>
>> I would suggest you 2 solutions:
>> 1. to use a function to edit these fields. That way you will be sure
>> that you will not forget to update both tables.
>> 2. use a trigger on master table. If I understand you - the history
>> table could be filled using trigger, when data in master table changes.
>> That way you could change only master table in your application.
>>
>>
> I think having triggers would bring performance down. So instead can
> we have single
> sql update statement to update all four fields of two tables? Just a
> crazy idea.
>
>
>> Good luck!
>>
>>
> Thanks for the wish.
> These wishes needed to implement smart solution to above problem :)
>
>
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can I update multiple table at a time?
@ 2008-10-10 11:07 H. Hall <hhall1001@reedyriver.com>
parent: Jagadeesh <mnjagadeesh@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: H. Hall @ 2008-10-10 11:07 UTC (permalink / raw)
To: Jagadeesh <mnjagadeesh@gmail.com>; pgsql-admin
Jagadeesh wrote:
> Hello Gurus,
>
> Is it possible to update two table using single sql statement? huh?
> let me be clear
>
> I have table master with state and date field. And in history table I
> have same fields i.e., state and date. I'm writing an update statement
> to set 'open' state records to 'closed' state after 7. both table can
> be joined using id.
>
> Any thoughts?
>
> Thanks
>
>
Update a view using the rule system?
See docs on rules:
http://www.postgresql.org/docs/8.3/static/rules-update.html
and this thread:
http://forums.devshed.com/postgresql-help-21/rules-views-and-updating-multiple-tables-58288.html
cheers,
HH
--
H. Hall
ReedyRiver Group LLC
http://www.reedyriver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can I update multiple table at a time?
@ 2008-10-10 13:18 Igor Neyman <ineyman@perceptron.com>
parent: Jagadeesh <mnjagadeesh@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Igor Neyman @ 2008-10-10 13:18 UTC (permalink / raw)
To: Jagadeesh <mnjagadeesh@gmail.com>; pgsql-admin
If you could declare a Foreign Key on two columns in "history" table
referencing "master" table, and declare this FK with "ON UPDATE CASCADE"
option, then postgres will update status in history table for you
whenever you update status in master table.
Igor
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Jagadeesh
Sent: Friday, October 10, 2008 1:12 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] can I update multiple table at a time?
Hello Gurus,
Is it possible to update two table using single sql statement? huh?
let me be clear
I have table master with state and date field. And in history table I
have same fields i.e., state and date. I'm writing an update statement
to set 'open' state records to 'closed' state after 7. both table can
be joined using id.
Any thoughts?
Thanks
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-10-10 13:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-10-10 05:12 can I update multiple table at a time? Jagadeesh <mnjagadeesh@gmail.com>
2008-10-10 05:53 ` Joshua Drake <jd@commandprompt.com>
2008-10-10 06:06 ` Julius Tuskenis <julius@nsoft.lt>
2008-10-10 07:54 ` Jagadeesh <mnjagadeesh@gmail.com>
2008-10-10 09:15 ` Julius Tuskenis <julius@nsoft.lt>
2008-10-10 11:07 ` H. Hall <hhall1001@reedyriver.com>
2008-10-10 13:18 ` Igor Neyman <ineyman@perceptron.com>
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