CODEWITHSUNDEEP
phpmysql
Mr. Engineer
Mr. Engineer

Reputation: 3515

Mysql single query with subquery or two separate queries

I've table with following structure : 

id | storeid | code

Where id is primary key

I want to insert data in this table with incremental order like this : 

id | storeid | code
1  |       2 | 1 
2  |       2 | 2 
3  |       2 | 3 
4  |       2 | 4

I've two solution to do this task.

1) Fire a query to get last record (code) from table and increment value of code with 1 using PHP and after that second query to insert that incremented value in database.

2) This single query : "INSERT INTO qrcodesforstore (storeid,code) VALUES (2,IFNULL((SELECT t.code+1 FROM (select code from qrcodesforstore order by id desc limit 1) t),1))"

I just want suggestion which approach is best and why for performance. I'm currently using second method but confuse about performance as I'm using three level sub query.

Upvotes: 0

Views: 125

Answers (4)

Kickstart
Kickstart

Reputation: 21513

Wrapping it up in a trigger:-

DELIMITER $$
DROP TRIGGER IF EXISTS bi_qrcodesforstore$$
CREATE TRIGGER bi_qrcodesforstore BEFORE INSERT ON qrcodesforstore
FOR EACH ROW
BEGIN
    DECLARE max_code    INT;
    SELECT MAX(code) INTO max_code FROM qrcodesforstore;
    IF max_code IS NULL THEN
        SET max_code := 1;
    ELSE
        SET max_code := max_code + 1;
    END IF
    SET NEW.code := max_code;
END

Upvotes: 0

mitkosoft
mitkosoft

Reputation: 5316

You simply can use INSERT with SELECT and MAX():

INSERT INTO qrcodesforstore 
(storeid, code) 
(SELECT 2, IFNULL((MAX(code)+1),1) FROM qrcodesforstore)

SQLFiddle

Upvotes: 1

drosam
drosam

Reputation: 2896

You can set the code column as AUTO_INCREMENT, you don't need to set it as primary key, see this.

Anyway, the second solution would be better, only one query is better than two.

Upvotes: 0

Karthick Kumar
Karthick Kumar

Reputation: 77

enter image description hereyou declare the field as primary key and unique and auto increment key they automatically increment the values

Upvotes: 0

Related Questions