Update data in mysql column field without removing previous value

Question

I am trying to update "new" column value with new value but problem is my query remove previous data while inserting new value

What is want: here is example table structure,

Table name = agg_lvl primary key set = uid

uid     | new
--------|--------
1       | 100
2       | 300

You can see "new" has 100 points, for example I send 100 new points to user 1, so new column value should be 100 + 100 = 200, right now with this code

$query4 = mysql_query("INSERT INTO agg_lvl (uid, new) VALUES ('$uid','$new')
ON DUPLICATE KEY UPDATE uid='$uid',new='$new'");

Not sure what

new = '$new'

I have tried both ways but no success = >

new = 'new + $new' or new = new + '$new'

2nd: `new=new+$new` exectly = `ON DUPLICATE KEY UPDATE new=new+$new` — splash58, May 01 '16 at 08:27
near miss :( so the problem was because of '' i remove it as per your instructions and now able to get my desire results — Goarge Go, May 01 '16 at 09:00
please post an answer, in comments i have no control to accept your answer, i am asking this because your answer is perfect and credit should gone to where it due, so it's due on you — Goarge Go, May 01 '16 at 09:12

score 2 · Accepted Answer · answered May 01 '16 at 09:54

2

You should make changes in your query

Make num = nun+$num to add new value to old one
Remove quotes arount $new because it is a number but not a string
Remove uid from set list because insert already point to that record

And your query should look so:

$query4 = mysql_query("INSERT INTO agg_lvl (uid, new) VALUES ('$uid','$new')
ON DUPLICATE KEY UPDATE new=new+$new");

answered May 01 '16 at 09:54

splash58

26,043
3
22
34

one new and strange thing happened – Goarge Go May 17 '16 at 08:12
could you a little bit more? – splash58 May 17 '16 at 08:32
it's working Perfect, one new and strange thing happened, the problem is since "id" i set Auto Increment and primary key, so when this query fire, it inserting new row every-time, the reason is " ON DUPLICATE KEY UPDATE " not working, because of " KEY " case, do you have any idea about how to deal with should i need to assign unique name to column " date " so we able to update row if date is same, if yes than how ? so we have total 4 columns in db , ( id, uid, new, date ) – Goarge Go May 17 '16 at 09:16
what is uid in this case? – splash58 May 17 '16 at 09:23
uid is coming from form posting – Goarge Go May 18 '16 at 03:23
i found something here, but not sure how to use in my case http://stackoverflow.com/questions/18749516/mysql-insert-on-duplicate-key-with-more-than-one-unique-key – Goarge Go May 18 '16 at 03:26
**i am getting results like this** id is key here and i set it auto-increment: – Goarge Go May 18 '16 at 03:35
id | uid | new | date – Goarge Go May 18 '16 at 03:36
--------|------|---------|---------------- – Goarge Go May 18 '16 at 03:36
100 | 1 | 100 | 2016-05-18 – Goarge Go May 18 '16 at 03:36
99 | 1 | 100 | 2016-05-18 – Goarge Go May 18 '16 at 03:37
**while i want results like this** – Goarge Go May 18 '16 at 03:37
id | uid | new | date – Goarge Go May 18 '16 at 03:37
-------|-------|---------|---------------- – Goarge Go May 18 '16 at 03:37
99 | 1 | 200 | 2016-05-18 – Goarge Go May 18 '16 at 03:38

georoot · Answer 2 · 2016-05-01T08:42:34.283

0

Okay first i will answer with the proper way to do the same, In this case i am assuming that UID is unique, so you make a new table scorecard with UID as foreign key. Now rather than update, you just insert stuff to table like if UID 1 gains 10 and 20 points, there are two entries. onw with 10 and one with 20. Now to get his current points, you add all points where UID=1 .

Now in your implementation the correct query would be

UPDATE userData SET points = points + x WHERE UID = $uid

where x is the new points gained and points is the name of column

edited May 01 '16 at 08:42

answered May 01 '16 at 08:27

georoot

3,557
1
30
59

@Rahul thanks for the catch, happened because i added the where statement in an edit and forgot to check the syntax again :) – georoot May 01 '16 at 08:43
1

2nd: new=new+$new exectly = ON DUPLICATE KEY UPDATE new=new+$new – splash58, this answer is working for me now – Goarge Go May 01 '16 at 09:01
@Goarge Go and why have you accepted not the answer that give you the result ? :) Accept your one – splash58 May 01 '16 at 09:03
because the guy who answer send comments he didn't posted answer, i guess he is new here – Goarge Go May 01 '16 at 09:06
when i try to accept my answer it says " accept your own answer in next 2 days ? – Goarge Go May 01 '16 at 09:07

Goarge Go · Answer 3 · 2016-05-01T09:09:46.493

0

$query4 = mysql_query("INSERT INTO agg_lvl (uid, new) VALUES ('$uid','$new') ON DUPLICATE KEY UPDATE uid='$uid',new=new+$new");

worked for me with help of @splash58

edited May 01 '16 at 09:09

answered May 01 '16 at 09:01

Goarge Go

57
1
8

Update data in mysql column field without removing previous value

3 Answers3

Linked