Oct
08

Copy SQL Row Changing ID

vjeux MySQL 2011-10-08

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!

If you liked this article, you might be interested in my Twitter feed as well.
 
All Posts
«
»
  • http://alphaopen.myopenid.com/ 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.

  • http://twitter.com/Gaarv1911 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.

 

Random Posts

  • February 20, 2010 -- Javascript – Slug (2)
    A slug is a way to represent a title with a limited charset (only lowercase letter and dash) to be inserted in the url. Even if it is a common function there is no good enough implentation when you Google for it. Here are the features I needed: No multiple dashes. ---- is converted to - No wr...
  • December 7, 2010 -- Javascript – MAX_INT: Number Limits (3)
    As I read an article about solving the 8-queen problem storing the board in a 64bit integer (French) I wanted to test it in Javascript. I knew that numbers where not stored as int64 but who knows, maybe it would have worked! As you may have expected, it failed, giving completly off results. The r...
  • August 25, 2009 -- Test – Are you a Javascript Guru? (0)
    Javascript is a very flexible language, I made a compilation of some edge cases that you may have encountered while programming. The main goal is to point out some interesting specific behaviors. Concatenation 1] var result = [10] + 1; [10, 1] "101" 11 Explanation: The Array doesn't hav...
  • February 23, 2012 -- Dassault Systemes Javascript Evangelism Talk (0)
    I recently had the chance to do a 2-hour Javascript evangelism talk at Dassault Systèmes. Unfortunately the presentation has not been recorded. I reused my the presentation I did at EPITA at the beginning and added a second part with a lot of demos. I've written down notes about the second part so y...
  • January 11, 2012 -- Javascript Ray Tracer (1)
    Here is a report of the Ray Tracer written by myself Christopher Chedeau. I've taken the file format and most of the examples from the Ray Tracer of our friends Maxime Mouial and Clément Bœsch. The source is available on Github. It is powered by Open Source technologies: glMatrix, CodeMirror, Cof...