I need to change only half of the records in a MySQL table

Question

I need to change only half of the records in a MySQL table

Navigation

#1 by (2 votes)

3

I made this procedure in MySQL to change a record only half of the table but it produces an inexplicable error:

It seems that trouble is in%% with% refusing to accept a variable.

In SQLServer I used LIMIT to solve the problem, but in MySQL I can not 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

asked by anonymous 07.01.2015 / 03:59

1 answer

Multiplying values within a for 500 Action does not contain method named

score 2 · Accepted Answer

An alternative is to mount the query on 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')