Vjeux

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!