CODEWITHSUNDEEP
mysql
Neru
Neru

Reputation: 95

How to delete a certain value from columns in mysql

I`m here because I have a issue that I can't solve and any help would be appreciated!

The situation is:

I'm coding a little item system in JS and using mysql as database, for every item I have the following columns:

Item_Name, Owner_1, Owner_2, Owner_3.

Each item can only be owned by 3 persons,but the person that owns the item can sell it, after he does, the code have to remove his name from the Owner_x that he belongs to. And here is the issue, I can't manage to figure how to do it This is the query that I do to search if the player has a certain item:

SELECT * FROM sys WHERE (owner_1= '${msg.author.id}' OR 
owner_2 = '${msg.author.id}' OR owner_3 = '${msg.author.id}') 
AND Item_Name = 'Dark Sword'

Now I need to remove it's name from the owner_x column that has his name in it. If someone can help me I will be very much grateful for it! (sorry for my English I'm not fluent)

Upvotes: 1

Views: 129

Answers (1)

Igor Ilic
Igor Ilic

Reputation: 1368

You could use something like this:

UPDATE sys SET 
Owner_1 = IF(Owner_1 = '${msg.author.id}', NULL, Owner_1),
Owner_2 = IF(Owner_2 = '${msg.author.id}', NULL, Owner_2),
Owner_3 = IF(Owner_3 = '${msg.author.id}', NULL, Owner_3)
WHERE '${msg.author.id}' IN(owner_1,owner_2,owner_3) AND Item_Name = 'Dark Sword'

Upvotes: 1

Related Questions