Replacing specific value in MySQL Database

Question

Lately I've been trying to figure out a way in order to replace only one part of a column on a specific user. The system is made for a game where the items column is layered (-1, -1, -1, -1, -1, -1, -1, -1)... I need a way to replace only one of those values with a new one, but when I can't seem to figure out how to do it.

As an example of what I need to achieve OLD ITEMS - (-1, -1, -1, -1, -1, -1, -1, -1) NEW ITEMS - (1838, -1, -1, -1, -1, -1, -1, -1)

next time you do the same thing it'd replace the next -1 value.

Big thank you to anyone that can help me figure out how to do this.

-- EDIT -- I forgot to mention we have over 4000 accounts. so the replacement script will be specified to One of the accounts only.

This is exactly why we always try to advise people to NORMALIZE THEIR DATABASE — Mark Baker, Jan 10 '14 at 10:17
Seems like a pretty bad schema to me. Why all these columns? Why not a table in which you insert (or update) records as required? — eggyal, Jan 10 '14 at 10:17
The database was pre-built by the team that worked on the game before the current team. that consists of me and ~10 other people. I am the head database developer and community developer, We have about 12 tables that all are specific to soemthing. One of the tables is for items in your inventory / backpack. — Luciferus, Jan 10 '14 at 10:22
@Luciferus: Are the specified -ve values represent *single row - multi column* or the *multi row - single column* values? — Ravinder Reddy, Jan 10 '14 at 10:22
@Ravinder One inventory is one row so 8 values layered out as an array. Every character has one inventory meaning one row each character and they can achieve multiple inventories during the time of playing. — Luciferus, Jan 10 '14 at 10:28
@Luciferus: Best advise is to update your posting with all these explanation included. Also include possible sample data like, if case one set 1838 else if case four set 1948, etc. — Ravinder Reddy, Jan 10 '14 at 10:37

score 0 · Answer 1 · answered Jan 10 '14 at 10:19

0

If the data is in one column of the database you can try the replace command

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘(-1, -1, -1, -1, -1, -1, -1, -1)’, ‘(1838, -1, -1, -1, -1, -1, -1, -1)’);

answered Jan 10 '14 at 10:19

tally

11
2

The information for the items column change as the users change their items or find new things in the game so the "-1" is an empty slot and 8 in a row is an empty inventory. The item columns could be pretty much everything from -1, 1 - 65535 so this wouldn't work too well since I need to find a -1 in a specific slot and replace only that one slot. – Luciferus Jan 10 '14 at 10:21
Oew.. you would have to use some kind of regex replace for mysql... found more solutions here: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – tally Jan 10 '14 at 10:26

score 0 · Answer 2 · answered Jan 10 '14 at 10:37

You should look into breaking those arrays into normalised data, for the sake of speed and usability. However, the solution is:

break your string with substring index.

set @a='(-1, -1, 2, -1, -1, -1, -1, -1)';
set @pos=3;
SELECT 
concat(
SUBSTRING_INDEX(@a, ',', @pos-1), -- before item 3
' ,', -- separator
SUBSTRING_INDEX(SUBSTRING_INDEX(@a, ',', @pos),',', -1), -- item 3
' ,', -- separator
SUBSTRING_INDEX(@a, ',', -(8-@pos)) -- after item 3, in an array of 8 items; you could find array length dinamically by replacing comas with "" and comparing new string length to old length +1
)
   >'(-1, -1, 2, -1, -1, -1, -1, -1)'

to update:

update table user_items
set items= concat(
    SUBSTRING_INDEX(items, ',', @pos-1), -- before item 3
    ' ,', -- separator
    'NEW ITEM HERE',
    ' ,', -- separator
    SUBSTRING_INDEX(items, ',', -(8-@pos)) -- after item 3
    )
where user_id=1

Replacing specific value in MySQL Database

2 Answers2