Add previous line in mysql

Asked

Viewed 502 times

4

I have a launch line, in which this line would need to receive the value of the previous line +1, some example of how to do this?

Table structure:

CREATE TABLE `lancamento` ( 
`data` VARCHAR(10) NOT NULL, 
`descricao` VARCHAR(30) NOT NULL, 
`debito` DOUBLE(20,2) NOT NULL, 
`credito` DOUBLE(20,2) NOT NULL, 
`saldo` DOUBLE(20,2) NOT NULL, 
`lancamento` DOUBLE(20,2) NOT NULL 
) ENGINE=INNODB
mysql sql web-application

  • You need to do this before entering the record?

  • It has to be all in SQL ? Where does the release data come from ?

  • Yes, in this case you could do something like a SELECT by taking the value of the previous line and then adding it to 1 by receiving the value of the next line, the problem would be the SELECT in which you should select the previous line.

  • am doing with php and mysql, the release would start at 1 and each run would generate a release, this release is just an identifier for control and also future changes.

  • If you just leave the field like AUTO INCREMENTO doesn’t solve? Or you already have another field AUTO INCREMENTO?

  • Figure out how to do it?

  • Put the structure of your table in the question so I can help you better. To get the structure, just run the command SHOW CREATE TABLE nome_da_tabela.

  • lancamentoSELECT * FROM caixa WHERE 1

  • @Wel Execute this command SHOW CREATE TABLE nome_da_tabela and put here the result

  • I’m not getting it very well, do you want to see the right output? it’s something like this

  • Let’s go continue this discussion in chat.

Show 6 more comments

2 answers

1


The MySQL already has a native option for this purpose, just inform that it should use the AUTO INCREMENTO when creating the table, as this option was not made can also be informed through a ALTER TABLE:

ALTER TABLE `lancamento`   
  CHANGE `lancamento` `lancamento` INT NOT NULL AUTO_INCREMENT  FIRST,
  CHANGE `data` `data` VARCHAR(10) CHARSET latin1 COLLATE latin1_swedish_ci NOT NULL  AFTER `lancamento`, 
  ADD PRIMARY KEY (`lancamento`);

From now on, you don’t need to inform the field lancamento at the time of giving the INSERT and automatically the MySQL will generate an incremented number:

INSERT INTO `lancamento` (`data`, `descricao`, `debito`, `credito`, `saldo`)
VALUES ('', '', 0, 0, 0);

  • It worked as needed, thank you very much for your patience, I’m still terrible with database.

0

I imagine you will have to check the value you want to add 1 and save in a variable, making a select to take the value.

Lock tables TABELA write;

$VALORSALVO = SELECT VALORATUAL FROM TABELA ;

Taking the value you want, you must use the variable you received by adding 1 in it, making a common Index. 

INSERT INTO TABELA( ID, VALORNOVO, )
VALUES ( 3 , $VALORSALVO+1);

unlock tables;

Notice I’ve set the table ( Lock tables TABELA write ), during the operation to ensure that we can make the proposed logic and finally unlock ( unlock tables; ) .

  • In this case it would select the last inserted table?

  • No, you have to inform the table in select and in Insert, in your select you will get what you want to add 1 and in Insert add 1 to it

  • That’s the problem, I can’t think of a way to automatically search for the last line, how could I select it to make that addition? since the identifier is what I want to add. I believe that if I had a way to make a SELECT only with the biggest release would solve my problem.

  • You can do this at Rigger de Insert, this very code

Browser other questions tagged mysql sql web-application

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