My own auto-increment

Question

I have usual 'id' in my table, but I also need a 'code', which would be like id, but year specific. That means it will be somehow auto-increment, but will start with 1 for each new year. When I insert new row, I simply use

 SELECT MAX(code) WHERE YEAR(time) = YEAR(CURRENT_DATE)

and increment by one.

But my project is already running and I would like to have a query, which would set these codes for already existing rows.

So now my table looks like this ->

Text 2011 'NULL' || Text 2011 'NULL' || Text 2011 'NULL' || Text 2012 'NULL' || Text 2012 'NULL'

and I need to change it to

Text 2011 1 || Text 2011 2 || Text 2011 3 || Text 2012 1 || Text 2012 2

Any ideas?

Answer 1

I think you could use a query like this one:

 SELECT T1.id, T1.year, COUNT(T2.id) AS new_id
 FROM `table` T1
 JOIN `table` T2 ON T1.id > T2.id AND T1.year = T2.year
 GROUP BY T1.id, T1.year
 ORDER BY T1.year, new_id

Just change for an UPDATE one. It could take some time though, depending on table size.

EDIT - Update query:

 CREATE TABLE temp_table (
   id INT NOT NULL,
   new_id NOT NULL,       
   PRIMARY KEY (id)
 );

 INSERT INTO temp_table
   SELECT T1.id, COUNT(T2.id) AS new_id
   FROM `table` T1
   JOIN `table` T2 ON T1.id > T2.id AND T1.year = T2.year
   GROUP BY T1.id, T1.year
   ORDER BY T1.year, new_id;

 UPDATE `table` t
   SET t.new_id = (SELECT tmp.new_id FROM temp_table tmp WHERE tmp.id = t.id)
 WHERE t.new_id IS NULL;

 DROP TABLE temp_table;

Not tested but should be pretty close. Replace table by your table name and column names id, new_id and year.

Answer 2

You can use BEFORE INSERT trigger to change NULL values, e.g. -

CREATE TABLE years(
  `year` INT(11) DEFAULT NULL,
  code INT(11) DEFAULT NULL
);

DELIMITER $$

CREATE TRIGGER trigger_name
  BEFORE INSERT
  ON years
  FOR EACH ROW
BEGIN
  IF NEW.code IS NULL THEN
    SET NEW.code = (SELECT IFNULL(MAX(code), 0) + 1 FROM years WHERE year = NEW.year);
  END IF;
END$$

DELIMITER ;

INSERT INTO years VALUES(1, NULL),(2, NULL)...

Answer 3

First fetch your last record, let suppose

Year |  id
2012 |  4

if(Year = current_year){

id = your_last_id+1;
year = current_year;

insert in your Table

} else {

id = 1;
year = current_year;

}