Using variable as integer for AUTO_INCREMENT

Question

I'm rather new to fiddling around in SQL and MySQL, although I stumbled upon this problem. I'm trying to get a number of how many rows are there in one table and set this number onto anothers AUTO_INCREMENT value. The problem is, MySQL workbench triggers a syntax error when I try to assign value via a variable. I tried to convert the query to unsigned integer, although not sure if it did work. Query for row amount returns the required number. What am I doing wrong?

SET @size = CONVERT((SELECT TABLE_ROWS FROM information_schema.tables WHERE table_name='Persons' and table_schema = 'Movies2'), unsigned);
ALTER TABLE Movies2.Actors AUTO_INCREMENT=@size;

Why would you want to do this?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2013-05-30 17:43:22 +00:00
Commented May 30, 2013 at 17:43 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 30, 2013 at 17:43

Savas Vedova · Accepted Answer · 2013-05-30 17:39:19Z

1

The following should work:

ALTER TABLE Movies2.Actors AUTO_INCREMENT = (SELECT COUNT(*) FROM Movies2.Persons);

answered May 30, 2013 at 17:39

Savas Vedova

5,7023 gold badges31 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using variable as integer for AUTO_INCREMENT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related