code Smell - select max(numeroProntuario)+1, but how to fix it?

Asked

Viewed 29 times

0

I have a table Paciente mysql, in it I have the column id, nome, numeroProntuario etc. This column numeroProntuario is defined by the increment of +1 resulting from select max(numeroProntuario) (legacy code, don’t judge me). Now, I’m implementing a waiting list, and I’m integrating with this table, and as I noticed this problem, I thought I’d use the same id number on the chart number. But I haven’t found anything in that direction and I still can’t change the previous records.

Does anyone have any idea how I fix this, or has no fix and better play it anyway?

Patient Table:

id      numeroProntuario    nome
5565    5548                ANA
5566    5549                PRISCILA
5567    5550                MIRIAN 
5568    5551                ALEXANDRE
5569    5552                JACONIAS 
5570    5553                KASSIO 
5571    5554                KEROLY 
5572    5555                ALEX
mysql encoding-style

  • You want to stop using select max(numeroProntuario)+1?

  • I think this is bad practice, so if possible yes. I would like to fix this

1 answer

1


Hello, Validates this solution: Change the column to auto_increment and set its current value:

ALTER TABLE Paciente MODIFY COLUMN numeroProntuario INT auto_increment;
ALTER TABLE Paciente AUTO_INCREMENT = select max(numeroProntuario)+1 from Paciente;

UPDATE:

Since the table can be only 1 Columa auto_increment, the value increment can be done via Trigger and select removed from the application:

CREATE TRIGGER SetNumeroProntuario BEFORE INSERT ON Paciente 
FOR EACH ROW BEGIN
  SET NEW.numeroProntuario = select max(numeroProntuario)+1 from Paciente;
END

  • I tried, but just in case I tried again: ALTER TABLE Patient MODIFY COLUMN numeroReply INT auto_increment Error Code: 1075. Incorrect table Definition; there can be only one auto column and it must be defined as a key 0.047 sec. And have more, I cannot lose or modify previous records.

  • this select max(numeroProntuario)+1, runs in the application.

  • 1

    I understood the problem, I updated the answer.

  • That’s right. I’m going to do some more tests here, but I believe this will do. Just one thing, I want to put it on standby, just like id. So I went like this: SET NEW.numeroProntuario = select max(id) from Patient;

  • 1

    If the file will match the id, test if it works like this: SET NEW.numeroReady = NEW.id;

Browser other questions tagged mysql encoding-style

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