MySQL Update if row exists, otherwise insert

Question

INSERT INTO GameScoreTotal (
    `GameName`
    , `OverallScore`
    , `GraphicsScore`
    , `StoryScore`
    , `GameplayScore`
    , `TimeScore`
    )
VALUES (
    'HomeFront'
    , '1'
    , '1'
    , '1'
    , '1'
    , '5'
    )
WHERE GameName = 'HomeFront'
    ON DUPLICATE KEY

UPDATE OverallScoreTotal = OverallScoreTotal + '1'
    , GraphicsTotal = GraphicsTotal + '1'
    , StoryTotal = StoryTotal + '1'
    , GameplayTotal = GameplayTotal + '1'
    , TimeTotal = TimeTotal + '1'
    , RatingCount = RatingCount + 1;

I am trying to update GameScoreTotal If we have something there otherwise insert. Any ideas?

John Woo · Accepted Answer · 2012-11-12 00:52:05Z

7

you need to define key on GameName name, (a unique or primary key should do)
remove WHERE clause from the insert statement

query,

INSERT INTO GameScoreTotal ( `GameName` , `OverallScore` , `GraphicsScore`
                           , `StoryScore` , `GameplayScore`  , `TimeScore` )
VALUES ( 'HomeFront' , 1  , 1 , 1 , 1 , 5 )
    ON DUPLICATE KEY
UPDATE OverallScoreTotal = OverallScoreTotal + 1
     , GraphicsTotal = GraphicsTotal + 1
     , StoryTotal = StoryTotal + 1
     , GameplayTotal = GameplayTotal + 1
     , TimeTotal = TimeTotal + 1
     , RatingCount = RatingCount + 1;

SOURCE

INSERT ... ON DUPLICATE KEY UPDATE Syntax

answered Nov 12, 2012 at 0:52

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John3136 · Accepted Answer · 2012-11-12 00:50:54Z

0

Lookup REPLACE INTO instead of INSERT INTO.

answered Nov 12, 2012 at 0:50

John3136

29.3k4 gold badges55 silver badges77 bronze badges

Collectives™ on Stack Overflow

MySQL Update if row exists, otherwise insert

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related