SQL Question

[KingMofo]

Hello all.

I use SQL quite a lot. When i am updating a table, sometimes i want to insert a new column into an existing table but the only way i can seem to do that is if i drop the table and re-create the table with the added column which results in all the data in the table being lost. I was wondering if anyone knows how to add a column to the table without having to drop it and re-create it?

An example:

Here's my current table columns

How would i add an extra column called "Account" without dropping and re-creating?

I would really appreciate any help on this.

Thanks in advanced.

[dzek (varez)]

this is for mysql, but should work in same way in sqlite (read sqlite manual maybe)?

ALTER TABLE `wiki_user` ADD `test` INT

[50p]

It would work varez but unfortunately ALTER TABLE doesn't work in MTA. http://www.sqlite.org/lang_altertable.html

It's a bug or "feature" or it's blocked for "security reason".

In theory, you could copy the table where you want to add a column, create a new table with a new column and copy all the data from old table (the backup table) to the new one.

http://www.sqlite.org/faq.html#q11

But it didn't work either when I tried it long time ago.

[dzek (varez)]

wow, weird.. security reasons? it could be better to block "drop table"

But it didn't work either when I tried it long time ago.

what? this workaroud HAVE TO work

[KingMofo]

It would work varez but unfortunately ALTER TABLE doesn't work in MTA. http://www.sqlite.org/lang_altertable.html

It's a bug or "feature" or it's blocked for "security reason".

In theory, you could copy the table where you want to add a column, create a new table with a new column and copy all the data from old table (the backup table) to the new one.

http://www.sqlite.org/faq.html#q11

But it didn't work either when I tried it long time ago.

Oh wow, i just read that page about transferring it to another table and it sounds not a bad idea. I will try it out and let you know how it goes.

Cheers you 2.

[eAi]

That'd be quite a slow thing to do with a large table.

I'd generally avoid adding columns to tables like this - unless it's a kind of administrative task.

[AdiBoy]

oh, but alter table works, i've used it before, im still using it when updating my scripts

get the admin panel up, resources tab and execute this command:

executeSQLQuery( "ALTER TABLE ADD COLUMN (size) DEFAULT " )

the DEFAULT option is not mandatory, you can skip it but you might also find it useful

[KingMofo]

oh, but alter table works, i've used it before, im still using it when updating my scripts

get the admin panel up, resources tab and execute this command:

executeSQLQuery( "ALTER TABLE ADD COLUMN (size) DEFAULT " )

the DEFAULT option is not mandatory, you can skip it but you might also find it useful

Oh wow! I just tried that and it worked!

Thank you all for your help and thank you AdiBoy.