CODEWITHSUNDEEP
mysql
Golinmarq
Golinmarq

Reputation: 1006

Insert a field in the table that depends of the values previously set

I have a table called Flights. This table has the following structure:

+----+----------+---------------+
| Id |   Name   | DepartureDate |
+----+----------+---------------+
|  1 | 20130001 | 2013-02-14    |
|  2 | 20130002 | 2013-05-04    |
|  3 | 20140001 | 2014-01-16    |
|  4 | 20130003 | 2013-07-15    |
|  5 | 20150001 | 2015-01-01    |
|  6 | 20150002 | 2015-09-14    |
+----+----------+---------------+

As you can see, flight's name depends of the year of the departure date and a correlative number.

The problem is that when I want to insert a flight in, i.e. 2013, I have to make a SELECT COUNT(*) query to get the quantity of flights of that year in this table and then make the INSERT INTO statement. I think this is terrible and inefficient, because it could be duplicated flight numbers.

What do you recommend to make this operation?

Upvotes: 0

Views: 66

Answers (3)

Golinmarq
Golinmarq

Reputation: 1006

In the end, I used a trigger event using the suggestions of @Alex. 

CREATE TRIGGER `flights_iu_trigger` BEFORE INSERT ON `flights` 
FOR EACH ROW 
BEGIN
    DECLARE flight_name INT;
    SELECT (IFNULL(MAX(Name), CONCAT(YEAR(NEW.DepartureDate),'0000')) + 1) 
    INTO flight_name FROM flights 
    WHERE YEAR(flights.DepartureDate) = YEAR(NEW.DepartureDate); 
    SET NEW.Name = flight_name;
END;

With these procedure I automatically set the name of the new flight according its year of departure

Upvotes: 0

JuanBoca
JuanBoca

Reputation: 724

If you are using (or can switch to) MyISAM Engine, you should use MySQL's AUTO_INCREMENT.

To watch this working: http://sqlfiddle.com/#!9/c6af9/1

CREATE TABLE flights (
  year ENUM('2013','2014','2015') NOT NULL,
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  DepartureDate date NOT NULL,
  PRIMARY KEY (year,id)
) ENGINE=MyISAM;

Then insert as you were doing it before or like this:

INSERT INTO flights (year, DepartureDate) VALUES ('2013', date('2013-07-03')), 
('2014', date('2014-06-03')), ('2013', date('2013-02-06')), 
('2015', date('2015-01-04')), ('2015', date('2015-02-04')),
('2015', date('2015-12-10')), ('2015', date('2015-06-04')),
('2015', date('2015-11-04')), ('2015', date('2015-10-04')),
('2015', date('2015-01-07')), ('2015', date('2015-01-09')),
('2015', date('2015-01-09')), ('2015', date('2015-09-12')),
('2015', date('2015-02-09')), ('2015', date('2015-07-04'));

And when looking for records do something like:

SELECT CONCAT(year, LPAD(id, 4, '0')) AS name, DepartureDate FROM flights;

For further reference: http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

Upvotes: 1

Alex
Alex

Reputation: 17289

I am still not sure of what is your goal.

But just my guess:

http://sqlfiddle.com/#!9/8dc5b/1

INSERT INTO flights 
SELECT 7, MAX(Name)+1, @d 
FROM flights
WHERE YEAR(DepartureDate)=YEAR(@d);

or 

INSERT INTO flights 
SELECT 7, MAX(Name)+1, date('2013-05-04') 
FROM flights
WHERE YEAR(DepartureDate)=YEAR(date('2013-05-04'));

or if your id is already AUTO_INCREMENT:

INSERT INTO flights 
SELECT null, MAX(Name)+1, date('2013-05-04') 
FROM flights
WHERE YEAR(DepartureDate)=YEAR(date('2013-05-04'));

Upvotes: 0

Related Questions