I'm trying to run an INSERT query into a table with 3 columns. The first column is where I'm having the issue.
It is called COMM_CODE with VARCHAR value of 10 length, and is the primary key, ALLOW NULL is unchecked.
The values for COMM_CODE look like this:
COMM_CODE
c20188
c20189
c20190
// and so on
What I would like to do, is when a new record is inserted, to basically add 1 to the most recent record.
Therefore, the most recent record is:
c20190
So when I add a new record, the COMM_CODE for the new record will be:
c20191
I tried this:
INSERT INTO table_c
(COMM_CODE, COMM_DESC, DATE)
VALUES
(''+1, 'VIDEO GAMES', NOW());
But that just adds a number 1 to that column.
How can I make this happen?
Here the solution for your query : To generate the new code I'hv created get_new_code function in mysql. I hope you know how funcions work in mysql.
CREATE FUNCTION `get_new_code`() RETURNS varchar(11)
BEGIN
Declare var_code VARCHAR(11);
SELECT max(`COMM_CODE`) INTO var_code FROM table_c;
RETURN (CONCAT('c',(convert(substr(var_code,2,length(var_code)), SIGNED INTEGER)+1)));
END
Just to verify your logic you can use :
select get_new_code();
So that you will get the clear picture. Call this get_new_code function in insert query like this :
INSERT INTO
`table_c`(`COMM_CODE`, `COMM_DESC`, `COMM_DATE`)
VALUES
(get_new_code(),'Description text',NOW());
This should solve your problem. :)