I need to change only half the records of a Mysql table

Asked

Viewed 961 times

3

I made this Procedure in Mysql to change a record only half of the table but it produces an unexplained error:

It seems the trouble is in the LIMIT who refuses to accept a variable.

On the Sqlserver I wore the TOP (50) PERCENT solved the problem, but in Mysql I can’t find a solution. Can anyone help me?

BEGIN
DECLARE CodigoNovaLista INT;
DECLARE QuantidadeTotal INT;
DECLARE QuantidadeListaNova INT;
DECLARE QuantidadeListaAtual INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

START TRANSACTION;
SELECT QuantidadeTotal = TotalEmails FROM tabelalistas WHERE IDLista = @IDListaAtual;

SET QuantidadeListaAtual =  QuantidadeTotal /2;
SET QuantidadeListaNova = QuantidadeTotal - QuantidadeListaAtual;

INSERT INTO tabelalistas (Nome, IDCliente) VALUES(@NovoNome, @IDCliente); 
SELECT CodigoNovaLista = LAST_INSERT_ID();

UPDATE tabelaemailsimportados SET IDLista=CodigoNovaLista WHERE IDLista = @IDListaAtual LIMIT QuantidadeListaNova;

UPDATE tabelalistas SET TotalEmails=QuantidadeListaAtual WHERE IDLista = @IDListaAtual ;
UPDATE tabelalistas SET TotalEmails=QuantidadeListaNova WHERE IDLista = CodigoNovaLista ;

COMMIT;
END
mysql sql-update
  • 2

    It seems very simple but I don’t understand the reason "only change half of the records of a table". In the code above has a chunk with INSERT and then comes some strange updates that seems to exchange the id list with the new one.. But I didn’t understand the meaning of it. By the way, I didn’t understand anything. hehehe

  • Can create a SQL Fiddle demonstrating the problem? There is nothing wrong in using LIMIT in the UPDATE (see the manual). Must be some syntax problem, but it gets hard to test without an example.

  • Another thing, if you change about 50% of the records and in any order solves the problem, there is an easier method UPDATE tabelaemailsimportados SET IDLista=CodigoNovaLista WHERE IDLista = @IDListaAtual AND RAND() < 0.5.

1 answer

2


An alternative is to mount the query to a text variable and run it dynamically with the command EXECUTE.

Example:

EXECUTE 'UPDATE tabelaemailsimportados ' ||
    ' SET IDLista=CodigoNovaLista WHERE IDLista = ' || 
    to_char(@IDListaAtual, '9') || 
    ' LIMIT ' || to_char(@QuantidadeListaNova, '9')

Browser other questions tagged mysql sql-update

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