Copy SQL Row Changing ID

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:

  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!
  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!
  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!

If you liked this article, you might be interested in my Twitter feed as well.
  • Alpha

    Hi! Maybe you could create a view, were you enumerate the different attributes, except for the ID. For example: "v_spells".

    Then you would do:
    INSERT INTO spell
    SELECT *
    FROM v_spell

    The problem with that is that in order to copy a specific spell, you would have to use another condition and not the ID itself.

    Also, it does not save you that much writing (as you have to write the field names in the view as well), but if you find yourself doing this a lot, then it will save you a little time.

  • Gem

    Okay I tried the following :

    create temporary table spell2 select * from spell where id=10000;
    update spell2 set id=12345;
    insert into spell select * from spell2;
    drop table spell2;

    And it seems to fill the purpose. spell2 can be dropped to continue statements or will be dropped if connexion is closed. Having to use another table seems the only way.


Related Posts

  • October 30, 2009 MySQL – Select Previous/Next Entries (0)
    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 […]
  • December 7, 2011 Automatic Links with Trie (0)
    On MMO-Champion, we often paste World of Warcraft patch notes taken from Blizzard. The main problem is that it's plain text. We want to be able to add links to all the spells, quests, zones ... This way people can mouseover and see the description. It helps figuring out what […]
  • August 29, 2011 Javascript: Improve Cache Performance: Reduce Lookups (2)
    In my Binary Decision Diagram Library, the performance bottleneck was the uniqueness cache. By reducing the number of cache lookup, it is possible to greatly improve the performances. Common pattern In order to test if the key is already in the cache, the usual pattern is to use key […]
  • December 2, 2012 One Mistake Sequence (5)
    I'm working a lot with URLs that contain ids and very often, I made a mistake in one digit of the long id and end up with a completely different element. If I don't pay attention, then I end up looking at two elements thinking they are the same and am intrigued until I find out the […]
  • September 25, 2011 Javascript Object Difference (5)
    This article is about a difference algorithm. It extracts changes from one version of an object to another. It helps storing a smaller amount of information. Template In a project, I have a template object with all the default settings for a widget. var template = { […]