Multiple update in a configuration table

Question

Multiple update in a configuration table

Navigation

#1 by (1 votes)

2

I would like to know the best method to perform update in a table that I have where there are 3 columns:

ID, Key, Value

Today I'm doing this:

UPDATE dbuf.dmds SET situacao = '0' WHERE id_demanda = '1';
UPDATE dbuf.flxs SET valor = '1' WHERE id = '22' AND chave = 'votos';
UPDATE dbuf.flxs SET valor = '4.5' WHERE id = '22' AND chave = 'tempo_etapa';
UPDATE dbuf.flxs SET valor = '2015-02-13 16:42' WHERE id = '22' AND chave = 'ult_mod';

Imagine that this is in a concatenated single text command, but I know the bank will do 1 instruction at a time, so I would like to know if I can do the 'one shot' procedure for there are some errors, do not create 'orphan' values in the table.

Thanks in advance!

mysql

asked by anonymous 13.02.2015 / 19:44

1 answer

How to generate colored labels in comparison.cloud? Error trying to create a connection to the database (MySQL)

score 1 · Answer 1

I imagine you will have to run the 4 separate updates yourself, this is because each one changes in different searches (where), but to not have 'orphaned' data, you can use mysql transactions ( link )

An example of how it would look like this:

START TRANSACTION;
UPDATE dbuf.dmds SET situacao = '0' WHERE id_demanda = '1';
UPDATE dbuf.flxs SET valor = '1' WHERE id = '22' AND chave = 'votos';
UPDATE dbuf.flxs SET valor = '4.5' WHERE id = '22' AND chave = 'tempo_etapa';
UPDATE dbuf.flxs SET valor = '2015-02-13 16:42' WHERE id = '22' AND chave = 'ult_mod';
COMMIT;