Multiple update in a configuration table

Question

Multiple update in a configuration table

Asked 9 years, 9 months ago

Viewed 139 times

2

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

ID, Key, Value

Today I’m doing so:

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 in a single text command, but I know that the database will do 1 instruction at a time, so I wonder if I have how to do the procedure 'in one shot', in case there is an error, do not create 'orphaned' values in the table.

From now on, I thank you all!

So @Juarez, the business rule is right, because they are 4 parameters that are properties of the entity dmds (demands). The table was built this way (id, key, value), because in the middle of the path it can assume different configuration keys (time_step can cease to exist in the system, or be renamed). I understand that it is difficult even... Thank you for your attention!

– Alisson Santos

2015/02/13 at 19:06

1 answer

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.

by Marcelo Diniz • **3,356** points · Answer 1 · 2015-02-13T18:57:06+00:00

I imagine you will have to run the 4 separate updates even, this is because each one changes in different searches (Where), but to not have 'orphaned' data you can use mysql transactions (http://dev.mysql.com/doc/refman/5.0/en/commit.html)

An example of what it would look like:

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;