I increment numerical value if there is same record , or insert wants to do 0 if there is not it

Question

Please help me.

select * from hoge where id=xxx;

If there is a data, I do it to do it, but though I implement this step, there is if in a program, and insert hoge set data=0 is troublesome if update hoge set data=data+1, a result are 0 lines.

May not you realize this procedure by a blow by SQL? replace hoge select id, data+1 as data from hoge where id = x; When it was this SQL, a result was not usable because data did not enter in the case of NULL.

After all will not there be it whether it is a plural number or a comb by SQL in an if sentence? If there is a simpler method, please teach it.

People thanking you in advance.

Answer 1

You seem to be asking how to create a row if it doesn't already exist or, if it does exist, update one of the fields in it.

The normal approach (for DBMS' that don't provide some form of UPSERT statement) is:

insert into TBL(keycol,countcol) values (key,-1)
update TBL set countcol = countcol + 1 where keycol = key

You need to ignore any errors on the first line (which should happen if the row already exists). Then the second statement will update it.

It's also unusual to initially insert a zero entry since you're generally adding one 'thing' the first time you do it. In that case, the insert would set the value to 0, not -1.

Answer 2

If I'm understanding the question properly (I don't think the OP is a native English speaker), you can use ON DUPLICATE KEY to do this in MySQL.

INSERT INTO table 
  (column1, column2, ...)
VALUES
  ('initial value for column1', 'initial value for column2', ...)
ON DUPLICATE KEY UPDATE
  column1 = column1 + 1, column2 = 'new value for column2';