• From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Topic: (My)SQL help

OK... so I have a small problem tongue
As some of you know, I'm working on a game, Cosmic Command.
I'm working on building units, but the problem is, they're supposed to be dynamic. So I don't have a column in my ships table for each unit, but I have a table that holds all units a ship has.
Here for my problem:
On unit creation, I want to attempt updating an existing value, and if that fails, insert a new row. A bit like the insert into ... on duplicate key update query, but in reverse.

Or should I just add a row at ship creation with the amount at 0?

Help would be appreciated!

-- Bekko

  • CodeXP
  • Senior Member
  • Offline
  • From: /dev/null
  • Registered: 2004-07-07
  • Posts: 503

Re: (My)SQL help

Wouldn't it be possible to use REPLACE INTO? It's a bit slower, but if I understand you correctly it should do what you're thinking of...

CodeXP's Website

  • Registered: 2006-06-29
  • Posts: 324

Re: (My)SQL help

you really should not store the units in your ship table.  create a table or two to link units to type (if thats needed) to ship.

4 Reply by elbekko (edited by elbekko 2006-10-07 20:38)

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: (My)SQL help

MadHatter wrote:

you really should not store the units in your ship table.  create a table or two to link units to type (if thats needed) to ship.

That's what I did. Read better wink

CodeXP wrote:

Wouldn't it be possible to use REPLACE INTO? It's a bit slower, but if I understand you correctly it should do what you're thinking of...

Hrmm... I'll check. Thanks.

EDIT: Nope, won't work. I need it to try updating an existing row, and if that fails insert a new one.
Maybe it can be done with an IF in the query?

  • Registered: 2006-06-29
  • Posts: 324

Re: (My)SQL help

hrm... took a few times of reading to understand that.

select first, then update or insert.

you should be able to create a function that would take care of it.

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: (My)SQL help

Well... I took the easy way out and just insert rows with the amount set to 0 at ship creation.
When building units, the row just gets updated. Far easier.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: (My)SQL help

insert into...on duplicate key update should work (well, I guess it depends on how you have your unique keys set up) tongue

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: (My)SQL help

Smartys wrote:

insert into...on duplicate key update should work (well, I guess it depends on how you have your unique keys set up) tongue

No it won't tongue I can't make ship_id or unit_type unique, as each user will have more than one unit type wink

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: (My)SQL help

Whatever, I have unit_type and fleet_id as a joint unique index tongue

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: (My)SQL help

Myeah, well, it works now ^^ This way should be faster anyway...