[MySQL 5.0.45-community-nt-log] Use LIMIT in query Update

Hello there, I need your help.

I have this output with query COUNT and GROUP BY Identified and store, execute in tbl_quantity:

	
quantity	Identified	store
12		115961		NY
3		115961		BS

With server side language (ASP NET) the user choose quantity for Identified.

I need update the selected quantity and not make available the number of rows quantity.

e.g.:
The user choose quantity 5 for Identified 115961 and store NY, this is the new output:

	
quantity	Identified	store
7		115961		NY
3		115961		BS

And I tried this query (I’m not sure of this solution…)


UPDATE `tbl_quantity` AS t
JOIN `tbl_registry` AS e ON t.store = e.store
SET available = 'No'
WHERE
	1
AND Identified = '115961'
AND t.store = 'NY'
AND id IN (
	SELECT
		id
	FROM
		(
			SELECT
				id
			FROM
				`tbl_quantity`
			ORDER BY
				id ASC
			LIMIT 0,
			5 <=== the user choose `quantity`
		) tmp
);

I have this error, can you help me?
Thank you.

[Err] 1052 - Column 'id' in IN/ALL/ANY subquery is ambiguous

I think you need to use t.id IN ( so it knows to use the tbl_quantity.id instead of tbl_registry.id

thank you for help.

You have right, I tried your suggestion and query has no error… but update not working.
See this:

UPDATE `tbl_quantity` AS t
JOIN `tbl_registry` AS e ON t.store = e.store
SET available = 'No'
WHERE
	1
AND Identified = '115961'
AND t.store = 'NY'
AND id IN (
	SELECT
		t.id
	FROM
		(
			SELECT
				id
			FROM
				`tbl_quantity`
			ORDER BY
				id ASC
			LIMIT 0,
			5 <=== the user choose `quantity`
		) tmp
);

Affected rows: 12
Time: 0.007ms

I hope your help.

I was thinking more of

UPDATE `tbl_quantity` AS t
JOIN `tbl_registry` AS e ON t.store = e.store
SET available = 'No'
WHERE
	1
AND Identified = '115961'
AND t.store = 'NY'
AND t.id IN (
	SELECT
		id
	FROM
		(
			SELECT
				id
			FROM
				`tbl_quantity`
			ORDER BY
				id ASC
			LIMIT 0,
			5 <=== the user choose `quantity`
		) tmp
);

thank you, but your last version I have:
Affected rows: 0
Time: 0.007ms

I have a tendency to complicate my life… and your life … :confused:

Solved with:

UPDATE `tbl_quantity`
SET available = 'No'
WHERE
	1
AND Identified = '115961'
AND store = 'NY'
ORDER BY
	Identified ASC
LIMIT 3;

Just heads up, it’s best practice to use BOOLEAN type instead of ‘Yes’ / ‘No’ varchars.

Thank you for suggestion.