SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 14/09/2017, 03:47 PM   #1
Skream
Banned
 
Join Date: Apr 2012
Posts: 225
Reputation: 64
Default MYSQL Right syntax for this

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)
Skream is offline   Reply With Quote
Old 14/09/2017, 04:09 PM   #2
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,083
Reputation: 2655
Default Re: MYSQL Right syntax for this

A subquery needs to be a full fledged query.

PHP Code:
UPDATE `player_inventorySET `amount` = `amount` + (SELECT `amountFROM player_inventory WHERE `id` = %dWHERE `id` = %
And oh, don't use quotes around integer values. It prevents indexes from being used which will needlessly slow down your queries.
__________________
Vince is offline   Reply With Quote
Old 14/09/2017, 04:57 PM   #3
Skream
Banned
 
Join Date: Apr 2012
Posts: 225
Reputation: 64
Default Re: MYSQL Right syntax for this

Quote:
Originally Posted by Vince View Post
A subquery needs to be a full fledged query.

PHP Code:
UPDATE `player_inventorySET `amount` = `amount` + (SELECT `amountFROM player_inventory WHERE `id` = %dWHERE `id` = %
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
Skream is offline   Reply With Quote
Old 14/09/2017, 05:15 PM   #4
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,083
Reputation: 2655
Default Re: MYSQL Right syntax for this

Oh. Uhm, well it is a rather strange query. Do you mind showing your data structure? Perhaps there is better way to do it.
__________________
Vince is offline   Reply With Quote
Old 14/09/2017, 05:26 PM   #5
Skream
Banned
 
Join Date: Apr 2012
Posts: 225
Reputation: 64
Default Re: MYSQL Right syntax for this

Quote:
Originally Posted by Vince View Post
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
Skream is offline   Reply With Quote
Old 14/09/2017, 05:47 PM   #6
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,083
Reputation: 2655
Default Re: MYSQL Right syntax for this

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.
__________________
Vince is offline   Reply With Quote
Old 14/09/2017, 05:52 PM   #7
Skream
Banned
 
Join Date: Apr 2012
Posts: 225
Reputation: 64
Default Re: MYSQL Right syntax for this

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

Last edited by Skream; 14/09/2017 at 06:24 PM.
Skream is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mysql queriesYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for th audriuxxx Scripting Help 4 11/04/2014 07:42 PM
MySQL Syntax MrMou6 Scripting Help 6 17/10/2013 04:36 PM
MySQL Syntax MrMou6 Scripting Help 1 12/10/2013 03:17 PM
MYSQL syntax!? Jstylezzz Scripting Help 2 17/04/2012 02:51 PM


All times are GMT. The time now is 01:41 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.