SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [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`:
    Code:
    	
    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:
    Code:
    	
    quantity	Identified	store
    7		115961		NY
    3		115961		BS
    And I tried this query (I'm not sure of this solution...)
    Code:
    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.
    Code:
    [Err] 1052 - Column 'id' in IN/ALL/ANY subquery is ambiguous
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I think you need to use t.id IN ( so it knows to use the tbl_quantity.id instead of tbl_registry.id
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for help.

    You have right, I tried your suggestion and query has no error... but update not working.
    See this:
    Code:
    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.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I was thinking more of
    Code:
    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
    );
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you, but your last version I have:
    Affected rows: 0
    Time: 0.007ms
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I have a tendency to complicate my life... and your life ...

    Solved with:
    Code:
    UPDATE `tbl_quantity`
    SET available = 'No'
    WHERE
    	1
    AND Identified = '115961'
    AND store = 'NY'
    ORDER BY
    	Identified ASC
    LIMIT 3;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  7. #7
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cms9651 View Post
    I have a tendency to complicate my life... and your life ...

    Solved with:
    Code:
    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.

  8. #8
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for suggestion.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •