How to get current id in INSERT INTO query and sum other number and copy to other column in mysql

Question

is there any way to get current auto increment id in INSERT INTO table and sum other number (1000) in mysql and insert to that row with on query;

INSERT INTO table_name (col1, col2,...) VALUES ('val1', CURRENT ROW ID+1000,...);

I can do with 2 query: first insert row and get last insert id sum insert id with number and update row with new value

but I want to do this by just one query if possible.

So, do you want the auto increment sequence to start from let's say 1000? — Darshan Mehta, Apr 28 '17 at 20:07
no I want to add some data to auto increment id and save to other column and insert to row — Jason, Apr 28 '17 at 20:12
In my opinion you should never change the primary key in a table, whether is from mysql or other db motor. Don´t make your db logic too complicated — Yazsid, Apr 29 '17 at 00:40

Darshan Mehta · Answer 1 · 2017-04-28T20:38:04.197

0

You can do it by declaring a variable and incrementing it with every row, e.g:

SELECT val, @i := @i + 1
FROM test, (SELECT @i := 1) a

If you want it to start from 1000 and add other data, you can initialise it with 1000.

Here's the SQL Fiddle.

edited Apr 28 '17 at 20:38

answered Apr 28 '17 at 20:17

Darshan Mehta

but i want to add current row auto increment id value to other number, not a single value – Jason Apr 28 '17 at 20:30
`@i` is current row auto increment id, what is the `other number` here? – Darshan Mehta Apr 28 '17 at 20:37

score 0 · Answer 2 · edited May 23 '17 at 12:17

0

SELECT 'AUTO_INCREMENT'
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';

You should be able to wrap this into a function, so that you can drop the function call into your insert query.

edited May 23 '17 at 12:17

Community

answered Apr 28 '17 at 20:18

Gratus D.

2 Answers2