[MySQL] Add points to guild function

[elnelson]

Yo! im working with this function, its suppose to add points to guild when they do X activities:

function doGuildAddPoints(guild_id, value)
  local func = db.executeQuery or db.query
  return func("UPDATE guilds SET points = ("..value.." + (SELECT points FROM guilds WHERE id = "..guild_id..")) WHERE id = "..guild_id)
end

But, im getting an error in console and can't add points (or balance, which its a similar query),this is the error i get:

mysql_real_query(): UPDATE guilds SET points = (3 + (SELECT points FROM guild WHERE id = 3))
-MYSQL ERROR: You cant specify target table 'guilds' for update in FROM clause (1093)

Post automatically merged: Jun 10, 2020

Solved:

To add values to a field i used a function then used the function in the query like this:

function getGuildPoints(guild_id)
local ret = db.getResult("SELECT `points` FROM `guilds` WHERE `id` = " ..guild_id)
 if ret:getID() == -1 then
    return -1
  end
  return ret:getDataInt("points")
end

function doGuildAddPoints(guild_id, value)
  local func = db.executeQuery or db.query
  db.executeQuery("UPDATE `guilds` SET `points` = "..getGuildPoints(guild_id).."+"..value.." WHERE `guilds`.`id` = "..guild_id)
 end

Hope this helps someone =)

 

[slaw]

Tip, you could also simply do:

UPDATE guilds SET points = points + x WHERE id = y;