SQL deleting

[[email protected]]

How do I delete tables/colums? =O

[napo_p2p]

For tables, you can just do:

NPC Code:

---

DROP TABLE table_name

---

Columns are a bit more tricky, because it turns out that SQLite has limited support for altering columns... you can check out: http://www.sqlite.org/faq.html#q11

[cbk1994]

Originally Posted by napo_p2p

For tables, you can just do:

NPC Code:

---

DROP TABLE table_name

---

Columns are a bit more tricky, because it turns out that SQLite has limited support for altering columns... you can check out: http://www.sqlite.org/faq.html#q11

What?

NPC Code:

---

DELETE FROM table_name WHERE var = 'val'

---

e.g.

NPC Code:

---

DELETE FROM jailed WHERE account = 'cbk1994'

---

EDIT: Sorry, I misread; I think that Andrew was talking about rows anyway, though.

And, for columns you can use

NPC Code:

---

ALTER TABLE table_name

DROP COLUMN column

---

[napo_p2p]

Originally Posted by cbk1994

And, for columns you can use

NPC Code:

---

ALTER TABLE table_name

DROP COLUMN column

---

For MySQL, yes. With SQLite (default database for Graal servers), you can only rename or add columns when altering tables. Dropping columns is not directly supported.

[cbk1994]

Originally Posted by napo_p2p

For MySQL, yes. With SQLite (default database for Graal servers), you can only rename or add columns when altering tables. Dropping columns is not directly supported.

Oh, thanks for correcting me

I've never actually done it myself.

[xXziroXx]

It's a good thing to vacuum after dropping a table.

NPC Code:

---

DROP TABLE table_name

VACUUM

---

[Inverness]

Originally Posted by xXziroXx

It's a good thing to vacuum after dropping a table.

NPC Code:

---

DROP TABLE table_name

VACUUM

---

Actually, no it isn't, considering HD space is not vital unless Stefan says so. SQLite uses that empty space for future data, it can write to the file without having to resize it.

In SQLite if you want to drop a column you should create a backup table. If you want to drop column b in a table named spelling that has columns a, b, c, you would do:

BEGIN
CREATE TABLE spelling_bak AS SELECT a, c FROM spelling
DROP TABLE spelling
CREATE TABLE spelling AS SELECT * FROM spelling_bak
COMMIT