It is possible to update more than 1 table in the same update statement?

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,

On Sun, Nov 7, 2010 at 3:11 PM, Scott Ribe <scott_ribe@killerbytes.com>wrote:

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,

On Sun, Nov 7, 2010 at 3:22 PM, Szymon Guz <mabewlun@gmail.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?

<http://www.postgresql.org/docs/9.0/interactive/tutorial-transactions.html&gt;

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

On Sun, Nov 7, 2010 at 3:41 PM, Scott Ribe <scott_ribe@killerbytes.com>wrote:

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.