CODEWITHSUNDEEP
sqlmysql
William
William

Reputation: 8808

How can I alter a temp table?

I need to create a temp table, than add a new int NOT NULL AUTO_INCREMENT field to it so I can use the new field as a row number. Whats wrong with my query?

SELECT post, newid FROM ((SELECT post`test_posts`) temp
ALTER TABLE temp ADD COLUMN newid int NOT NULL AUTO_INCREMENT)

edit: 

SELECT post, newid FROM ((SELECT post, newid as int NOT NULL AUTO_INCREMENT FROM `test_posts`) temp

This didn't work ether.

Upvotes: 0

Views: 5997

Answers (2)

a1ex07
a1ex07

Reputation: 37382

If you need a row number and don't want to actually create a temporary table, you can achieve it by using user variables.

SET @my_row_num =0;
SELECT @my_row_num := @my_row_num+1 as row_number, post, newid FROM ((SELECT post`test_posts`) temp;

Upvotes: 0

John Fisher
John Fisher

Reputation: 22717

Not that this means it's impossible, but I haven't seen any SQL version that will allow you to modify a table from within a SELECT. Pull the alter table out and make it a separate statement. After you fix any syntax issues, you should be good.

Also, it doesn't look like you actually have a "temp table" to alter. Rather, you're looking for a solution that will let you add an arbitrary id to the result of your "SELECT post..." query. I don't know which engine you're using, but a sequence counter, rowid, rownum, or other similar feature would better fit your needs.

Upvotes: 1

Related Questions