CODEWITHSUNDEEP
mysqlsql
Jordan Harris
Jordan Harris

Reputation: 77

Index on Select Insert?

I'm trying to use a SELECT INSERT for my project; however, one of the columns that needs filled with the insert corresponds to it's position in the INSERT. I've tried using a subquery (as seen below) and just using the table name in the FROM clause of the SELECT -- neither seems to work.

The output I get is always "10001" instead of what I expect (i.e. "10001", "10002", "10003", etc.)

Any help?

INSERT INTO table
 (
  Key2
 )
SELECT 
 (SELECT IF(CAST(MAX(t.Key2) as UNSIGNED) IS NULL, "10001", CAST(MAX(t.Key2) as UNSIGNED)+1) FROM (select * from table) t)
FROM table2

Upvotes: 0

Views: 1274

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

If you want enumerated keys, then use variables:

INSERT INTO table (Key2)
    SELECT COALESCE(maxkey, 0) + (@rn := @rn + 1)
    FROM table2 CROSS JOIN
         (SELECT MAX(t.key) maxkey, @rn := 0 FROM table) x ;

Upvotes: 2

Mackers
Mackers

Reputation: 1050

If I understand your question correctly, Try something like this:

INSERT INTO TABLE (KEY2)
SELECT A.KEY2 + B.MAX_VAL AS KEY2 FROM TABLE A
JOIN (SELECT MAX(KEY2) AS MAX_VAL FROM TABLE) B
ON 1 = 1

Upvotes: 0

Related Questions