更新SQL服务器查询问题 [英] SQL server query problem with update
问题描述
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)
我构建了一个名为 Price_Table $ c的测试数据库表$ c>如下所示: 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 namedFixup_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 namedPrice_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屋!