0
I need to change 1200 records only from one column in the table, generated the data in the generatedata, the rest of the columns must remain with the data, someone knows a quick way to this process?
1 answer
1
Where I work I’ve had situations like this and solved it in the following way:
BEGIN
--cursor com o select dos dados que irei realizar o update
for c_prod in (select a.id, a.produto FROM produtos a WHERE a.id IN (1,
23, 45, 46, 47, 49, 55 ...));
LOOP
--inserir todos os dados do cursor em uma tabela auxiliar
INSERT INTO produtos_temp VALUES (c_prod.id, c_prod.produto);
end loop;
--executando o update desejado
UPDATE produtos a SET a.status = 'atualizado'
SELECT b.* FROM produtos_temp b
WHERE a.id = b.id;
END;
This way you capture the data you want to update inside a cursor, in following in the implicit loop you insert the recovered id data in the auxiliary table so you can maintain a kind of backup of it, and finally executes the update of the data whose id is in this auxiliary table. Recalling that this requires a little knowledge in PL/SQL.
Browser other questions tagged database oracle
You are not signed in. Login or sign up in order to post.
Use the update with a Where clause whose id is larger than both and smaller than both
– Guilherme Lopes
@Guilhermelopes you can help me by giving a practical example?
– Roknauta
@Guilhermelopes meant for you to update within the id range where the 1200 records meet. Example would be something like this:
update tabela set coluna = newValor where id > 1 and id <=1200, taking into account that the 1200 records are sequential.– user28595
@Diegofelipe Thanks for the answer, id’s are not sequential :/
– Roknauta
What differs these 1200 records from the others? If I didn’t have something to filter them, there’s no way to do it automatically.
– user28595
If you give a sample of the data, the table structure and the result you want it is easier to help.
– Emanuel Rocha Costa
as diegofm said, if you don’t have something to filter them, that is, a string identifying those 1200 records, for example:
id,nome,exemplo,specialAi in the special column, would for example have an int that when searching for the 1200 records, they would have a number 1 in the special, and the rest would not, so they would not be affected– Sampaio Leal
Can make a query that results only in the records you need ?
– David