MYSQL Right syntax for this - Printable Version

MYSQL Right syntax for this - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MYSQL Right syntax for this (/showthread.php?tid=641333)

MYSQL Right syntax for this - Skream - 14.09.2017

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)

Re: MYSQL Right syntax for this - Vince - 14.09.2017

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.

Re: MYSQL Right syntax for this - Skream - 14.09.2017

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

Re: MYSQL Right syntax for this - Vince - 14.09.2017

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

Re: MYSQL Right syntax for this - Skream - 14.09.2017

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

Re: MYSQL Right syntax for this - Vince - 14.09.2017

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.

Re: MYSQL Right syntax for this - Skream - 14.09.2017

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