As the MySQL documentation mentions at the bottom of the UPDATE
documentation:
Currently, you cannot update a table and select from the same table in a subquery.
I've modified your query and I added another subquery:
update store_players as sp
set sp.credits=sp.credits+5000
where sp.name IN (
select a.name from (select sp2.name from syndicate_store2.store_players as sp2,
syndicate_ipb.nexus_purchases as np
left join syndicate_ipb.members as mem
on np.ps_member=mem.member_id
where sp2.authid = IntToSteam(mem.steamid)
and np.ps_item_id=15) AS a);
And without subqueries:
UPDATE test.store_players as sp
JOIN syndicate_ipb.nexus_purchases AS np ON (np.ps_item_id=15)
LEFT JOIN syndicate_ipb.members as mem on (np.ps_member=mem.member_id)
SET sp.credits=sp.credits+5000
WHERE sp.authid = IntToSteam(mem.steamid);
EDIT:
Query:
update store_players as sp
JOIN (select sp2.name,COUNT(sp2.name) AS cnt
from syndicate_store2.store_players as sp2,
syndicate_ipb.nexus_purchases as np
left join syndicate_ipb.members as mem
on np.ps_member=mem.member_id
where sp2.authid = IntToSteam(mem.steamid)
and np.ps_item_id=15
group by sp2.name) AS q
ON (q.name=sp.name)
set sp.credits=sp.credits+(5000*q.cnt);
PD: Replace the schema test
for your store_players
table schema.
Try it!
Answer to the new question:
SELECT `dept_name`
, `emp_name`
, `emp_sal`
, `ej_joining date`
FROM (
SELECT emp.`dept_name`, emp.`emp_name`, emp.`emp_sal`, emp.`ej_joining date`
, @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
, @dept_id := `emp_dept_id` as `dept_id`
FROM (
SELECT DT.`dept_name`
, EMP.`emp_name`, EMP.`emp_sal`, EMP.`emp_dept_id`
, EJ.`ej_joining date`
FROM `Dept` DT
INNER JOIN `Employee` EMP ON DT.`dept_id` = EMP.`emp_dept_id`
INNER JOIN `JoiningDate` EJ ON EJ.`ej_emp_id` = EMP.`emp_id`
WHERE DT.`dept_status` = '1' AND EMP.`emp_status` = '1'
ORDER BY Dt.`dept_id`, RAND()
) emp
CROSS JOIN (
SELECT @dept_id := 0 as dept_id, @num := 0 as num
) v
) num
WHERE num.num <= 2
ORDER BY `dept_name`, num.num
;
SQL Fiddle
This answers the original question
This first query will randomly order rows by dept_id
. Each row in each group of dept_id
is given a incremental number (num
) from 1 to n.
Random order query:
SELECT `emp_name`, `emp_sal`
, @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
, @dept_id := `emp_dept_id` as `emp_dept_id`
FROM (
SELECT `emp_name`, `emp_sal`, `emp_dept_id`
FROM `Employee`
ORDER BY emp_dept_id, RAND()
) emp
CROSS JOIN (
SELECT @dept_id := 0 as dept_id, @num := 0 as num, @numx := 0 as numx
) v
The Dept
table can then be joined to this query. A filter on num
value being 1 or 2 will give the first 2 rows of each dept_id
. SQL Fiddle
The order changes for each execution and the first 2 rows change as well.
Main query:
SELECT DT.`dept_name`, num.`emp_name`, num.`emp_sal`
FROM `DEPT` DT
INNER JOIN (
SELECT `emp_name`, `emp_sal`
, @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
, @dept_id := `emp_dept_id` as `emp_dept_id`
FROM (
SELECT `emp_name`, `emp_sal`, `emp_dept_id`
FROM `Employee`
ORDER BY emp_dept_id, RAND()
) emp
CROSS JOIN (
SELECT @dept_id := 0 as dept_id, @num := 0 as num
) v
) num
ON DT.`dept_id` = num.`emp_dept_id`
WHERE num.num <= 2
ORDER BY DT.`dept_name`, num.num
Best Answer
Let's assume these are the settings for the problem. These will be the tables:
And these are the values stored on the different tables:
At this point, we could run this query...
and get
Your original function (slightly modified so it is no actually a function and can be played on at dbfiddle, and with some extra info), would do the following:
(these would be your function parameters)
If you want to limit the tags that are checked, you can change the previous code just slightly:
That will now return:
The previous code will also work and choose all tags if you set
@tags_list
to NULL. In that case, the@tags_list IS NULL
is true, and the JOIN condition is equivalent to the original case.You can check everything at dbfiddle here