Thread: It is possible to update more than 1 table in the same update statement?
Hi,
I need to update various tables in the same update. It is possible to do it?
Best Regards,
I need to update various tables in the same update. It is possible to do it?
Best Regards,
On 7 November 2010 15:58, Andre Lopes <lopes80andre@gmail.com> wrote:
Hi,
I need to update various tables in the same update. It is possible to do it?
Best Regards,
Could you describe a little bit more what you want to achieve?
regards
Szymon
On Nov 7, 2010, at 7:58 AM, Andre Lopes wrote: > need to update various tables in the same update. It is possible to do it? Transactions??? -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Sorry for not explain well.
I mean update more than one table at the same time with something like this:
update table1, table2
set
table1.f1 = 'aaa',
table2.date = '2001-01-01'
where
table1.id = 'x1' and table2.id = 'x1'
Something like this is possible? It is the best way to do it?
Best Regards,
I mean update more than one table at the same time with something like this:
update table1, table2
set
table1.f1 = 'aaa',
table2.date = '2001-01-01'
where
table1.id = 'x1' and table2.id = 'x1'
Something like this is possible? It is the best way to do it?
Best Regards,
On Sun, Nov 7, 2010 at 3:11 PM, Scott Ribe <scott_ribe@killerbytes.com> wrote:
On Nov 7, 2010, at 7:58 AM, Andre Lopes wrote:Transactions???
> need to update various tables in the same update. It is possible to do it?
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
On Nov 7, 2010, at 8:20 AM, Andre Lopes wrote: > I mean update more than one table at the same time with something like this: Why? Use a transaction. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
On 7 November 2010 16:20, Andre Lopes <lopes80andre@gmail.com> wrote:
Sorry for not explain well.
I mean update more than one table at the same time with something like this:
update table1, table2
set
table1.f1 = 'aaa',
table2.date = '2001-01-01'
where
table1.id = 'x1' and table2.id = 'x1'
Something like this is possible? It is the best way to do it?
Best Regards,
This is not possible, however you could do two updates in one transaction.
regards
Szymon
The only way I can guarantee a transaction is in a Function or there are other ways?
Best Regards,
Best Regards,
On Sun, Nov 7, 2010 at 3:22 PM, Szymon Guz <mabewlun@gmail.com> wrote:
On 7 November 2010 16:20, Andre Lopes <lopes80andre@gmail.com> wrote:Sorry for not explain well.
I mean update more than one table at the same time with something like this:
update table1, table2
set
table1.f1 = 'aaa',
table2.date = '2001-01-01'
where
table1.id = 'x1' and table2.id = 'x1'
Something like this is possible? It is the best way to do it?
Best Regards,This is not possible, however you could do two updates in one transaction.regardsSzymon
On Nov 7, 2010, at 8:37 AM, Andre Lopes wrote: > The only way I can guarantee a transaction is in a Function or there are other ways? <http://www.postgresql.org/docs/9.0/interactive/tutorial-transactions.html> -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Hi,
Thanks for the reply.
I have write the transaction, but I have some doubt's... If in this example the Update is executed successfully and the Function it is not, what happens? The Update automatically rolls back?
Example:
[code]
Begin;
update aae_anuncios
set
n_anunciante = 'teste',
email = 'teste@email.com',
telefone_privado = '123456789',
dat_nasc = '1980-01-01',
n_anuncio = 'teste nome',
telefone_anuncio = '234567890',
id_genero = 'femi',
id_cidade = '1452003',
id_nacionalidade = 'BRAS',
id_orientacao = 'h'
where id_anuncio_externo = '38';
select apr_update_hist_2_1('aae_hist_anuncios',
'id_anuncio_externo',
'38', /* id_anuncio_externo */
'2010-08-31', /* data_inicio */
'2010-12-29', /* data_fim */
'AA' /* Motivo: Aprovação Anúncio */
);
commit;
[/code]
Best Regards,
Thanks for the reply.
I have write the transaction, but I have some doubt's... If in this example the Update is executed successfully and the Function it is not, what happens? The Update automatically rolls back?
Example:
[code]
Begin;
update aae_anuncios
set
n_anunciante = 'teste',
email = 'teste@email.com',
telefone_privado = '123456789',
dat_nasc = '1980-01-01',
n_anuncio = 'teste nome',
telefone_anuncio = '234567890',
id_genero = 'femi',
id_cidade = '1452003',
id_nacionalidade = 'BRAS',
id_orientacao = 'h'
where id_anuncio_externo = '38';
select apr_update_hist_2_1('aae_hist_anuncios',
'id_anuncio_externo',
'38', /* id_anuncio_externo */
'2010-08-31', /* data_inicio */
'2010-12-29', /* data_fim */
'AA' /* Motivo: Aprovação Anúncio */
);
commit;
[/code]
Best Regards,
On Sun, Nov 7, 2010 at 3:41 PM, Scott Ribe <scott_ribe@killerbytes.com> wrote:
On Nov 7, 2010, at 8:37 AM, Andre Lopes wrote:> The only way I can guarantee a transaction is in a Function or there are other ways?--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: It is possible to update more than 1 table in the same update statement?
From
Scott Marlowe
Date:
On Mon, Nov 8, 2010 at 5:39 PM, Andre Lopes <lopes80andre@gmail.com> wrote: > Hi, > > Thanks for the reply. > > I have write the transaction, but I have some doubt's... If in this example > the Update is executed successfully and the Function it is not, what > happens? The Update automatically rolls back? Yes, transactions (without savepoints) are all or nothing.