Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
guys I'm confuse with this following query
 
UPDATE dbo.ItemMaster_MPQ
SET
  PriceID = 1
WHERE
  count(PriceID) = 1 AND
  PriceID > 1
 

return is error:
 
1 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
 
pls solve this.
Posted 9-Mar-13 2:59am
Comments
Jαved at 9-Mar-13 8:07am
   
what do you want to do? please elaborate.
JMMS Karunarathne at 9-Mar-13 23:53pm
   
Sorry for summery, here is i want, i want update only PriceID column in ItemMaster_MPQ table that having PriceID value larger than 1 (PriceID>1) and priceID count =1 (count(PriceID)=1) (if not have duplicate) then update all that priceID as =1
Mike Meinz at 9-Mar-13 10:26am
   
You can't use COUNT() that way. Unless we know what you really want to do, we can't help you.
JMMS Karunarathne at 9-Mar-13 23:53pm
   
Sorry for summery, here is i want, i want update only PriceID column in ItemMaster_MPQ table that having PriceID value larger than 1 (PriceID>1) and priceID count =1 (count(PriceID)=1) (if not have duplicate) then update all that priceID as =1
Mike Meinz at 10-Mar-13 10:11am
   
Sorry, I still do not understand. You're using PriceId as a integer to be accumulated and as an ID to select records. Perhaps, DDL for the table and some sample before and after data will help communicate your requirement better.
JMMS Karunarathne at 10-Mar-13 16:31pm
   
My attempt is I have a table that having several price to single itemid an ex: item 00001 red chilly 1kg $45.00 and same item have $48 per 1kg. So first price level per kg is $45 & 2nd price level is $48 per kg. When first price level delete from price level tbl. Iwant single price level that having price level id 2 update to 1 if there is no else price level to same item id and if left price id larger than 1.
Mike Meinz at 10-Mar-13 18:33pm
   
Using your specification from the above reply, I developed Solution 2.
 
Your original question did not include the description of the table and did not include any reference to "itemid" so, as you see from my Solution 2, it was extremely difficult to suggest a solution.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Before execution of Fixup_PriceID
ItemID	Description	PriceLevel_ID	PKEY
100	Red Chilli 1KG	1	         32434
100	Red Chili 1KG	5	         52434
100	Red Chili 1KG	6	         62437
100	Red Chili 1KG	8	         82435
100	Red Chili 1KG	9	         93434
200	Green Chilli 2KG	1	         32235
300	Yellow Chilli 3KG	1	         24345
300	Yellow Chilli 3KG	2	         53453
400	Purple Chilli 3KG	1	         34534
 
After execution of Fixup_PriceID
ItemID	Description	PriceLevel_ID	PKEY
100	Red Chilli 1KG	1	         32434
100	Red Chili 1KG	2	         52434
100	Red Chili 1KG	3	         62437
100	Red Chili 1KG	4	         82435
100	Red Chili 1KG	5	         93434
200	Green Chilli 2KG	1	         32235
300	Yellow Chilli 3KG	1	         24345
300	Yellow Chilli 3KG	2	         53453
400	Purple Chilli 3KG	1 	         34534
 
Tested: SQL Server Express 2012
 
1. Create a Stored Procedure named Fixup_PriceID.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Mike Meinz
-- Create date: 12 March 2013
-- Description:	Fixup PriceLevel_ID to ensure ascending sequential integers 
--                within each ItemID
-- =============================================
CREATE PROCEDURE [dbo].[Fixup_PriceID] 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TEMPTABLE TABLE(
    PKey int,
	PriceLevel_ID int);
DECLARE TEMPTABLE_Cursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT PKey,PriceLevel_ID from @TEMPTABLE order by PKey;
DECLARE @tmpPKey int;
DECLARE @tmpPriceLevel_ID int;		
DECLARE @currItemID int,@currPkey int,@currPriceLevel_ID int;
DECLARE @lastItemID int,@lastPkey int,@lastPriceLevel_ID int;
Declare Price_Table_Cursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT ItemId,Pkey,PriceLevel_ID from PRICE_TABLE order by ItemID,Pkey;
DECLARE @FIRST_TIME BIT;
DECLARE @idx int;
DECLARE @boolSEQUENCE_ERROR BIT;
BEGIN TRANSACTION FIXUPPRICELEVELID;
SET @FIRST_TIME = 1;
OPEN Price_Table_Cursor;
-- Get first row
FETCH NEXT FROM Price_Table_Cursor INTO @currItemID,@currPkey,@currPriceLevel_ID;
WHILE @@FETCH_STATUS = 0
	BEGIN
	IF @FIRST_TIME = 1
	    BEGIN
		SET @FIRST_TIME=0
		SET @LastItemID=@currItemID
		SET @LastPKEY=@currPKEY
		SET @LastPriceLevel_ID=@currPriceLevel_ID
		SET @idx=0
		SET @boolSEQUENCE_ERROR=0
	    END
	
	IF @currItemID = @lastITemID
		-- Same ItemID as last time, insert the values into TEMPTABLE for later processing
		BEGIN
		SET @idx = @idx + 1
		INSERT INTO @TEMPTABLE (PKey,PriceLevel_ID) Values(@currPkey,@currPriceLevel_ID);
		-- Check to see if PriceLevel_ID is expected value
		IF @idx <> @currPriceLevel_ID SET @boolSEQUENCE_ERROR=1
		END
	ELSE
		-- New ItemID was found so start processing @lastItemID's rows
		BEGIN
		IF @boolSEQUENCE_ERROR = 1 
			-- Found at least one for the @lastItemID that was out of sequence
			BEGIN
			-- Rows for @lastItemID were out of sequence
			-- Check which rows are out of sequence and fix them
			SET @idx=1
			Open TEMPTABLE_Cursor;
			FETCH NEXT FROM TEMPTABLE_Cursor INTO @tmpPKey,@tmpPriceLevel_ID;
			WHILE @@FETCH_STATUS = 0
				BEGIN
				-- Check if PriceLevel_ID is expected value
				IF @idx <> @tmpPriceLevel_ID 
					-- If not expected value, Update PRICE_TABLE 
					BEGIN
					UPDATE PRICE_TABLE SET PriceLevel_ID=@idx WHERE ItemID=@lastItemID AND PKEY=@tmpPKey;
					END
				SET @idx=@idx + 1 -- Compute next expected PriceLevel_ID
				FETCH NEXT FROM TEMPTABLE_Cursor INTO @tmpPKey,@tmpPriceLevel_ID;
				END
			Close TEMPTABLE_Cursor;
			END
		DELETE FROM @TEMPTABLE;
		--
		-- Put @currItemID's information into TEMPTABLE
		-- 
		SET @boolSEQUENCE_ERROR=0
		SET @LastItemID=@currItemID
		SET @LastPKEY=@currPKEY
		SET @LastPriceLevel_ID=@currPriceLevel_ID
		SET @idx=1
		INSERT INTO @TEMPTABLE (PKey,PriceLevel_ID) Values (@currPkey,@currPriceLevel_ID);
		-- Check to see if PriceLevel_ID is expected value
		IF @idx <> @currPriceLevel_ID SET @boolSEQUENCE_ERROR=1
		END
		FETCH NEXT FROM Price_Table_Cursor INTO @currItemID,@currPkey,@currPriceLevel_ID;
	END -- WHILE loop
--
-- Process @lastItemID's rows
--
IF @boolSEQUENCE_ERROR = 1 
	-- Found at least one for the @lastItemID that was out of sequence
	BEGIN
	-- Rows for @lastItemID were out of sequence
	-- Check which rows are out of sequence and fix them
	SET @idx=1
	Open TEMPTABLE_Cursor;
	FETCH NEXT FROM TEMPTABLE_Cursor INTO @tmpPKey,@tmpPriceLevel_ID;
	WHILE @@FETCH_STATUS = 0
		BEGIN
		-- Check if PriceLevel_ID is expected value
		IF @idx <> @tmpPriceLevel_ID 
			-- If not expected value, Update PRICE_TABLE
			BEGIN
			UPDATE PRICE_TABLE SET PriceLevel_ID=@idx WHERE ItemID=@lastItemID AND PKEY=@lastPkey;
			END
		SET @idx=@idx + 1 -- Compute next expected PriceLevel_ID
		FETCH NEXT FROM TEMPTABLE_Cursor INTO @tmpPKey,@tmpPriceLevel_ID;
		END
	Close TEMPTABLE_Cursor;
	END
DELETE FROM @TEMPTABLE;
Close Price_Table_Cursor;
Deallocate TEMPTABLE_Cursor;
Deallocate Price_Table_Cursor;
COMMIT TRANSACTION;
END
 
2. Execute the Stored Procedure
Exec Fixup_PriceID
  Permalink  
v4
Comments
JMMS Karunarathne at 12-Mar-13 14:04pm
   
thank mike this is working great, but my ItemID data type is varchar(150) ihave edit your script to itemid as varchar(150) then it is not working.my table has more that 32000 item prices. so i cant change in ItemID data type. I have following columns in that table
 
Column Name Datatype
ItemID - varchar(150)
PriceID - int
PurchesPrice - numeric(18, 2)
SalePrice - numeric(18, 2)
ItemQTY - numeric(18, 3)
ID - PKEY
 
And very sorry for not giving you complete details of my problem earlier, please tell me how to edit your script to those data type.
Mike Meinz at 12-Mar-13 16:33pm
   
1. It looks like the only difference between my Data Types and your Data Types is for ItemID. You should edit the following two DECLARE statements in the Stored Procedure to look like this:
DECLARE @currItemID varchar(150),@currPkey int,@currPriceLevel_ID int;
DECLARE @lastItemID varchar(150),@lastPkey int,@lastPriceLevel_ID int;
 
2. I now assume that you use the term PKEY to mean PRIMARY KEY which is an INT. Use CTRL+H REPLACE to replace all PKey with ID. I already declared the variables for ID as INT.
 
3. Use CTRL+H REPLACE to replace all PriceLevel_ID with PriceID. I already declared the variables for PricedID as INT.
 
4. The other columns are not used in the Stored Procedure.
 
5. I will also add that using ID, the Primary Key, sort order to determine the PriceID values will eventually lead to a problem. For example, if someone deletes PriceId=2 for an item and later adds PriceId=2 back and then later deletes PriceID=1, What was PricedId=3 will become PriceId=1 and the last added PriceId=2 added will become the highest numbered PriceId.
JMMS Karunarathne at 13-Mar-13 3:27am
   
Mike you have saved me, your SP works great, Thank you for Helping me from your expertise. keep up with good work. IF I HAVE A PROBLEM I WILL DEFINITELY ASK YOU!
Mike Meinz at 13-Mar-13 6:51am
   
Thank you for your reply. Please mark Solution 3 with five stars to show that it worked for you.
 
I hope you learned something about how to ask a question.
* Make a small example table
* Show CREATE DDL and sample data for the example table
* Explain the sample data
* Explain the problem statement (The business requirement)
* Show and explain the coding problem (The syntax or bug problem)
* Include any error messages and indicate which line of the code throws the error
* etc.
JMMS Karunarathne at 14-Mar-13 0:45am
   
Mike i've total forgot that my i have 3 more tables interconected with PriceID in PriceLevel Table
1. ItemMaster_ExpDate - that having expiredates in each items in each price level
2. ItemMasterWholeSalePrices - that having wholesale margin quantity for each items each price Levels
3. ItemMaster_ReorderLevel - that having reorder levels quantity for each items each price Levels
 
each table schema as follows
 
01. ItemMaster_ExpDate Table
ExpireDateID numeric(18, 0)
ItemID varchar(150)
PriceID decimal(18, 0)
ManufactureDate Datetime
ExpireDate Datetime
 
02.ItemMasterWholeSalePrices table
ItemID varchar(150)
PriceID decimal(18, 0)
WholeSalePrice numeric(18, 2)
WholesaleMargin numeric(18, 3)
 
03. ItemMaster_ReorderLevel table
ItemID varchar(150)
ReorderLevel numeric(18, 3)
 
when i execu Fixup_PriceID SP , these tables PriceID also needs to update according to price level table PriceID Order. if not items expire date / wholesale margin / Reorder Level not link to each price level correctly. please help me on this. thanking you!
Mike Meinz at 14-Mar-13 7:59am
   
The fact that you have to change PriceID in the PriceLevel table or anywhere else is an indication that there is a problem in the design of the data. If you can change the data design, I suggest storing the ID (Primary Key) from the PriceLevel table as PriceLevel_Table_ID (Foreign Key) in those other three tables rather than the PriceId. That way, you never have to update them when the PriceID changes in the PriceLevel table. When you need to retrieve the PriceID, you can do a JOIN based on the ID (Primary Key) of the PriceLevel table and the Foreign Key PriceLevel_Table_ID stored in the three other tables. Also, I would not store the ItemID in other tables. I would store that only in the "ItemMaster" table and instead, use the ID (Primary Key) of the "ItemMaster" table as a Foreign Key in all of the other tables.
 
If change of the data design is not possible, then following is a solution.
 
Note that with this type of data reorganization going on, the Stored Procedure must be run when there is no other database activity to ensure that another process does not retrieve data from one table before the change and another after the change.
 
The following should be added to the Stored Procedure.
 
At the location of the two UPDATE statements in the Stored Procedure add an UPDATE statement for each of these other tables. Example:
UPDATE ItemMaster_ExpDate SET PriceID=@idx where ItemID=@lastItemID And PriceID=@tmpPriceID;
UPDATE ItemMasterWholeSalePrices SET PriceID=@idx where ItemID=@lastItemID And PriceID=@tmpPriceID;
UPDATE ItemMaster_ReorderLevel SET PriceID=@idx where ItemID=@lastItemID And PriceID=@tmpPriceID;
 
I will also add that using ID, the Primary Key, sort order to determine the PriceID values will eventually lead to a problem. For example, if someone deletes PriceId=2 for an item and later adds PriceId=2 back and then later deletes PriceID=1, What was PricedId=3 will become PriceId=1 and the last added PriceId=2 added will become the highest numbered PriceId.
JMMS Karunarathne at 14-Mar-13 12:32pm
   
Thanks Again mike, i will re-design data structure As you mentioned. although your update statements are working OK. thanks!
Mike Meinz at 14-Mar-13 16:38pm
   
A good rule to follow is never have a value that can be changed in more than one table and never use that value as a primary key or foreign key. Follow that rule and you will have far less problems developing and maintaining your database update software.
JMMS Karunarathne at 14-Mar-13 23:47pm
   
mike, can you mail me simple sql database with that technique with 3 or 4 tables then i can study it well before change my online database structure. thanking you!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this, although logic of your query is uncertain...
UPDATE dbo.ItemMaster_MPQ
SET PriceID = 1
WHERE PriceID > 1 AND PriceId IN (
    SELECT PriceId
    FROM dbo.ItemMaster_MPQ
    GROUP BY PriceId
    HAVING COUNT(PriceId)=1)
More about aggregate functions[^] and HAVING clause[^].
  Permalink  
Comments
JMMS Karunarathne at 9-Mar-13 23:47pm
   
Sorry guys for summery, here is i want,
 
i want update only PriceID column in ItemMaster_MPQ table that having PriceID value larger than 1 (PriceID>1) and priceID count =1 (count(PriceID)=1) (if not have duplicate) then update all that priceID as =1
Maciej Los at 10-Mar-13 6:44am
   
Did you tried my query. It meets your needs ;)
JMMS Karunarathne at 12-Mar-13 0:05am
   
Sorry maciej i have tried, butt it is not working on my requirement, but Mike one is done. but thanks anyway to take your time to consider.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I built a test database table named Price_Table that looks like the following:
ItemID   Description       PriceLevel_ID
100	Red Chili 1KG  	           2
200	Green Chili 2KG	           2
300	Yellow Chili 3KG	   1
300	Yellow Chili 3KG	   2
400	Purple Chili 3KG	   1
 
Then, I built this UPDATE statement and executed it:
UPDATE Price_Table SET PriceLevel_Id=1 WHERE ItemID NOT IN (
SELECT ItemID FROM Price_Table WHERE PriceLevel_ID=1);
 
To get this result:
ItemID   Description       PriceLevel_ID
100	Red Chili 1KG  	           1
200	Green Chili 2KG	           1
300	Yellow Chili 3KG	   1
300	Yellow Chili 3KG	   2
400	Purple Chili 3KG	   1
 
Note: This assumes there are only two price levels (PriceLevel_ID can only = 1 or 2).
 
Tested: SQL Server Express 2012
  Permalink  
v3
Comments
JMMS Karunarathne at 11-Mar-13 23:56pm
   
Thanks Mike it work like charm.
 
i have a more single problem
my table is
ItemID Description PriceLevel_ID AutogenID(PKEY)
100 Red Chilli 1KG 1 32434
200 Green Chilli 2KG 1 32235
300 Yellow Chilli 3KG 1 24345
300 Yellow Chilli 3KG 2 53453
400 Purple Chilli 3KG 1 34534
100 Red Chilli 1KG 5 52434
100 Red Chilli 1KG 8 82435
100 Red Chilli 1KG 9 93434
100 Red Chilli 1KG 6 62437
 
as in table item id 100 red chilli 1kg has several price id (1,5,8,9,6) some middle level price ID items are deleted(2,3,4,7) so i need to update existing Price id to order according to Auto generated (PKEY) column acceding value. PriceID should(1,2,3,4,5)
 
Please help me on this. Thanks!
Mike Meinz at 12-Mar-13 11:15am
   
Your original question did not include any information about PKEY or itemID and it did not show that there could be more than two PriceLevel_ID values. Because the new requirement is much more complicated than what you originally indicated, the solution is more complicated. I don't know of a single SQL statement that will perform as specified in your new requirement. This would have been a whole lot easier if you had included complete information in your original question. I hve always found that I get better answers and quickly if I provide complete information up front with small examples. I have created Solution 3 to satisfy this requirement. In Solution 3, you create a new Stored Procedure named Fixup_PriceID. The Fixup_PriceID Stored Procedure processes each ItemId's rows and ensures that the PriceLevel_IDs are in ascending integer order based on the ascending values of PKEY.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 295
1 OriginalGriff 249
2 Sergey Alexandrovich Kryukov 205
3 Aajmot Sk 197
4 Sinisa Hajnal 176
0 OriginalGriff 7,800
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 12 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100