更新SQL服务器查询问题 [英] SQL server query problem with update

查看：53 发布时间：2019/6/16 22:37:59 SQL-server-2005 SQL-Server

本文介绍了更新SQL服务器查询问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我很困惑以下这个问题

  UPDATE  dbo.ItemMaster_MPQ 
  SET  
 PriceID =  1  
 < span class =code-keyword> WHERE  
 count（PriceID）=  1   AND  
 PriceID>  1

返回错误：

1聚合可能不会出现在WHERE子句中，除非它位于HAVING子句或选择列表中包含的子查询中，并且聚合的列是外部的参考。

请解决此问题。

解决方案

执行Fixup_PriceID之前

 ItemID描述PriceLevel_ID PKEY 
 100红辣椒1KG 1 32434 
 100红辣椒1KG 5 52434 
 100红辣椒1KG 6 62437 
 100红辣椒1KG 8 82435 
 100红辣椒1KG 9 93434 
 200绿辣椒2KG 1 32235 
 300黄辣椒3KG 1 24345 
 300黄辣椒3KG 2 53453 
 400紫辣椒3KG 1 34534

执行Fixup_PriceID后

 ItemID描述PriceLevel_ID P. KEY 
 100红辣椒1KG 1 32434 
 100红辣椒1KG 2 52434 
 100红辣椒1KG 3 62437 
 100红辣椒1KG 4 82435 
 100红辣椒1KG 5 93434 
 200绿辣椒2KG 1 32235 
 300黄辣椒3KG 1 24345 
 300黄辣椒3KG 2 53453 
 400紫辣椒3KG 1 34534

经过测试：SQL Server Express 2012

1.创建一个名为的存储过程 Fixup_PriceID 。

 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
  -  ============================================= 
  - 作者：Mike Meinz 
  - 创建日期：2013年3月12日
  - 描述：Fixup PriceLevel_ID以确保递增的连续整数
  - 在每个ItemID 
中 -  ============================================= 
 CREATE PROCEDURE [dbo]。[Fixup_PriceID] 
 AS 
 BEGIN 
  -  SET NOCOU添加了NT ON以防止额外的结果集干扰SELECT语句。 
 SET NOCOUNT ON; 
 DECLARE @TEMPTABLE TABLE（
 PKey int，
 PriceLevel_ID int）; 
 DECLARE TEMPTABLE_Cursor CURSOR LOCAL FAST_FORWARD FOR 
 SELECT PKey，PriceLevel_ID来自PKey的@TEMPTABLE订单; 
 DECLARE @tmpPKey int; 
 DECLARE @tmpPriceLevel_ID int; 
 DECLARE @currItemID int，@ currPkey int，@ currPriceLevel_ID int; 
 DECLARE @lastItemID int，@ lastPkey int，@ lastPriceLevel_ID int; 
声明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; 
  - 获取第一行
 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 
  - 与上次相同的ItemID，将值插入TEMPTABLE以便以后处理
 BEGIN 
 SET @idx = @idx + 1 
 INSERT INTO @TEMPTABLE （PKey，PriceLevel_ID）值（@ currPkey，@ currPriceLevel_ID）; 
  - 检查PriceLevel_ID是否为预期值
 IF @idx<> @currPriceLevel_ID SET @ boolSEQUENCE_ERROR = 1 
 END 
 ELSE 
  - 找到新的ItemID所以开始处理@ lastItemID'的行
 BEGIN 
 IF @boolSEQUENCE_ERROR = 1 
  - 发现至少有一个@lastItemID不按顺序
 BEGIN 
  -  @lastItemID的行不按顺序
  - 检查哪些行不在序列并修复它们
 SET @ idx = 1 
打开TEMPTABLE_Cursor; 
 FETCH NEXT FROM TEMPTABLE_Cursor INTO @ tmpPKey，@ tmpPriceLevel_ID; 
 WHILE @@ FETCH_STATUS = 0 
 BEGIN 
  - 检查PriceLevel_ID是否为预期值
 IF @idx<> @tmpPriceLevel_ID 
  - 如果不是预期值，则更新PRICE_TABLE 
 BEGIN 
 UPDATE PRICE_TABLE SET PriceLevel_ID = @ idx WHERE ItemID = @ lastItemID AND PKEY = @ tmpPKey; 
 END 
 SET @ idx = @ idx + 1  - 计算下一个预期的PriceLevel_ID 
 FETCH NEXT FROM TEMPTABLE_Cursor INTO @ tmpPKey，@ tmpPriceLevel_ID; 
结束
关闭TEMPTABLE_Cursor; 
 END 
 DELETE FROM @TEMPTABLE; 
  -  
  - 将@currItemID'的信息放入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）值（@ currPkey，@ currPriceLevel_ID）; 
  - 检查PriceLevel_ID是否为预期值
 IF @idx<> @currPriceLevel_ID SET @ boolSEQUENCE_ERROR = 1 
 END 
 FETCH NEXT FROM Price_Table_Cursor INTO @ currItemID，@ currPkey，@ currPriceLevel_ID; 
 END  -  WHILE循环
  -  
  - 处理@ lastItemID'的行
  -  
 IF @boolSEQUENCE_ERROR = 1 
  - 找到至少有一个@lastItemID不按顺序
 BEGIN 
  -  @lastItemID的行不按顺序
  - 检查哪些行不按顺序并修复它们
 SET @ idx = 1 
打开TEMPTABLE_Cursor; 
 FETCH NEXT FROM TEMPTABLE_Cursor INTO @ tmpPKey，@ tmpPriceLevel_ID; 
 WHILE @@ FETCH_STATUS = 0 
 BEGIN 
  - 检查PriceLevel_ID是否为预期值
 IF @idx<> @tmpPriceLevel_ID 
  - 如果不是预期值，则更新PRICE_TABLE 
 BEGIN 
 UPDATE PRICE_TABLE SET PriceLevel_ID = @ idx WHERE ItemID = @ lastItemID AND PKEY = @ lastPkey; 
 END 
 SET @ idx = @ idx + 1  - 计算下一个预期的PriceLevel_ID 
 FETCH NEXT FROM TEMPTABLE_Cursor INTO @ tmpPKey，@ tmpPriceLevel_ID; 
结束
关闭TEMPTABLE_Cursor; 
 END 
 DELETE FROM @TEMPTABLE; 
关闭Price_Table_Cursor; 
 Deallocate TEMPTABLE_Cursor; 
 Deallocate Price_Table_Cursor; 
 COMMIT TRANSACTION; 
 END

2.执行存储过程

 Exec Fixup_PriceID

试试这个，虽然你的查询逻辑是不确定的......

 更新 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）

有关汇总功能的更多信息 ^ ]和 HAVING子句 [ ^ ]。

我构建了一个名为Price_Table 如下所示： ItemID描述PriceLevel_ID 100 Re d辣椒1KG 2 200绿辣椒2KG 2 300黄辣椒3KG 1 300黄辣椒3KG 2 400紫辣椒3KG 1 然后，我构建了这个UPDATE语句并执行它： UPDATE Price_Table SET PriceLevel_Id = 1 WHERE ItemID NOT IN （ SELECT ItemID FROM Price_Table < span class =code-keyword> WHERE PriceLevel_ID = 1）; 要获得此结果： ItemID描述PriceLevel_ID 100红辣椒1KG 1 200绿辣椒2KG 1 300黄辣椒3KG 1 300黄辣椒3KG 2 400紫辣椒3KG 1 注意：这假设只有两个价格级别（PriceLevel_ID只能= 1或2）。经测试：SQL Server Express 2012

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.

解决方案

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

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[^].

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


                        这篇关于更新SQL服务器查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！


                    
                        查看全文

更新SQL服务器查询问题 [英] SQL server query problem with update

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

更新SQL服务器查询问题 [英] SQL server query problem with update

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭