I've come across an SQL issue. I need to make a fake spell for the WoW database. However creating one from scratch is too annoying, there are like 30 non-NULL values to fill. Instead what I want is to copy an existing spell with the new id. It appeared to be less trivial than expected.
Working Solution
Let's say you want to copy the spell #1000 into the spell #12345. Here is the SQL command to do it:
INSERT INTO spell SELECT 12345 AS id, name, icon, /* ... fill all the column names */ FROM spell WHERE id = 10000; |
Trying to improve ...
It is really annoying to get the list of column names (You can get it using \d spell). What we would like to do is use the wildcard * like this:
-- This is not working! INSERT INTO spell SELECT *, 12345 AS id FROM spell WHERE id = 10000; |
The problem is that * also adds a column named id. Therefore there are two id columns ... Problem, now it no longer fits the spell structure.
What I need to make it work is to be able to remove a column from *. Here is something I'd like to write:
-- This is not working! INSERT INTO spell SELECT * \ id, 12345 AS id FROM spell WHERE id = 10000; |
I tried to simulate \ with JOIN, UNION but I couldn't get anything that works. If you have any idea, I'm really interested in!
