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

[cms9651]

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

[cpradio]

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

[cms9651]

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.

[cpradio]

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
);

[cms9651]

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

[cms9651]

I have a tendency to complicate my life... and your life ...

Solved with:

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

[K. Wolfe]

I have a tendency to complicate my life... and your life ...

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.

[cms9651]

Thank you for suggestion.