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!

In SQL, this is a common issue to query the previous and next entries of the database. In order to achieve this, we are going to use a table that is sorted by the `id` field. To get the previous, the technique consists in sorting all the fields that are lower that the current one and taking the first one.

SELECT *
FROM `table`
WHERE `id` < $current_id
ORDER BY `id` DESC
LIMIT 1

Let’s take an example with the values [8, 3, 4, 9, 6, 1]. We want to get the previous of 8.

• WHERE `id` < $current_id
  We first take all the values strictly lower than 8
  Result: [3, 4, 1, 6]
• ORDER BY `id` DESC
  We then sort them in descending order
  Result: [6, 4, 3, 1]
• LIMIT 1
  We take the first one
  Result: 6

You can apply the same method for the next entry:

SELECT *
FROM `table`
WHERE `id` > $current_id
ORDER BY `id` ASC
LIMIT 1