[Skream]

pawn Code:

UPDATE `player_inventory` SET `amount` = `amount` + (`amount` WHERE `id` = '%d')
                                      WHERE `id` = '%d'

What's the right syntax for this? I'm trying to do a combination between same column and same table but in diff rows

(this is for weapon/ammo combination)

[Vince]

A subquery needs to be a full fledged query.

PHP Code:

UPDATE `player_inventory` SET `amount` = `amount` + (SELECT `amount` FROM player_inventory WHERE `id` = %d) WHERE `id` = %d 


And oh, don't use quotes around integer values. It prevents indexes from being used which will needlessly slow down your queries.

[Skream]

Quote:

Originally Posted by Vince A subquery needs to be a full fledged query. PHP Code: UPDATE `player_inventory` SET `amount` = `amount` + (SELECT `amount` FROM player_inventory WHERE `id` = %d) WHERE `id` = %d  And oh, don't use quotes around integer values. It prevents indexes from being used which will needlessly slow down your queries.

Yeah that was my first attempt but when I use it I get

#1093 - Table 'player_inventory' is specified twice, both as a target for 'UPDATE' and as a separate source for data

[Vince]

Oh. Uhm, well it is a rather strange query. Do you mind showing your data structure? Perhaps there is better way to do it.

[Skream]

Quote:

Originally Posted by Vince Oh. Uhm, well it is a rather strange query. Do you mind showing your data structure? Perhaps there is better way to do it.

https://pastebin.com/2PycN5pr

[Vince]

Hmm, are you trying to add two identical rows together? Same player, same item id? Because I'm not quite grasping the context.

What you should probably do is:
a) remove the id field and the keys associated with it.
b) put the primary key on the combination of (item_id, pid) to make that combination unique.

Note that the primary key is by definition unique. An extra unique attribute is redundant. Also consider switching to the InnoDB engine. It's much faster and it uses row locks instead of table locks.

[Skream]

its not the same item ID. They have nothing in commom besides being in the same table and same PID. Its a combination system for items like fishroad and worms, weapon and ammo...

i'll switch for InnoDB ty. Maybe a JOIN or something would do the job in that case?

I think this fixed my problem

https://pastebin.com/quM3r2e2