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