UPDATE Uuser SET Ucount=Ucount-1 WHERE EXISTS (SELECT a.Uname FROM KJ a INNER JOIN KJ b ON a.Uname=b.ZuoZhe AND Uname=a.Uname AND b.KeJianID IN(376,378))
IF OBJECT_ID('Uuser') IS NOT NULL DROP TABLE Uuser CREATE TABLE Uuser(Ucount INT, Uname VARCHAR(50))INSERT INTO Uuser SELECT 4, 'tom1' UNION ALL SELECT 2, 'tom2' UNION ALL SELECT 4, 'tom3' UNION ALL SELECT 3, 'tom4' UNION ALL SELECT 5, 'tom5' UNION ALL SELECT 8, 'tom6' UNION ALL SELECT 1, 'tom7' IF OBJECT_ID('KJ') IS NOT NULL DROP TABLE KJ CREATE TABLE KJ(KeJianID VARCHAR(20), Zuozhe VARCHAR(50)) INSERT INTO KJ SELECT 1001, 'tom1' UNION ALL SELECT 1002, 'tom2' UNION ALL SELECT 1003, 'tom3' UNION ALL SELECT 1004, 'tom4' UNION ALL SELECT 1005, 'tom5' UNION ALL SELECT 1006, 'tom6' UNION ALL SELECT 1007, 'tom7' select * from UuserUPDATE Uuser SET Ucount = Ucount - 1 WHERE
(Uname IN ( SELECT Uname FROM Uuser JOIN KJ ON Uuser.Uname = KJ.Zuozhe WHERE (KJ.KeJianID IN (1004, 1002)) ) )select * from Uuser测试结果良好,没有问题。不知道你的问题出在哪。
借用下楼上兄弟的代码IF OBJECT_ID('Uuser') IS NOT NULL DROP TABLE Uuser CREATE TABLE Uuser(Ucount INT, Uname VARCHAR(50))INSERT INTO Uuser SELECT 4, 'tom1' UNION ALL SELECT 2, 'tom' UNION ALL SELECT 4, 'tom3' UNION ALL SELECT 3, 'tom4' UNION ALL SELECT 5, 'tom5' UNION ALL SELECT 8, 'tom6' UNION ALL SELECT 1, 'tom7' IF OBJECT_ID('KJ') IS NOT NULL DROP TABLE KJ CREATE TABLE KJ(KeJianID VARCHAR(20), Zuozhe VARCHAR(50)) INSERT INTO KJ SELECT 1001, 'tom1' UNION ALL SELECT 1002, 'tom' UNION ALL SELECT 1003, 'tom3' UNION ALL SELECT 1004, 'tom' UNION ALL SELECT 1005, 'tom5' UNION ALL SELECT 1006, 'tom6' UNION ALL SELECT 1007, 'tom7' select * from Uuser--使用游标插入 declare cur cursor for SELECT Uname FROM Uuser JOIN KJ ON Uuser.Uname = KJ.Zuozhe WHERE (KJ.KeJianID IN (1004, 1002))declare @Name varchar(100) open cur fetch cur into @Namewhile @@fetch_status=0 begin UPDATE Uuser SET Ucount = Ucount - 1 WHERE Uname=@Name fetch cur into @Name endclose cur deallocate cur--查看 select * from Uuser select * from KJ如果嫌这样麻烦就只有单个id的循环的执行
IF OBJECT_ID('Uuser') IS NOT NULL
DROP TABLE Uuser
CREATE TABLE Uuser(Ucount INT, Uname VARCHAR(50))INSERT INTO Uuser
SELECT 4, 'tom1' UNION ALL
SELECT 2, 'tom2' UNION ALL
SELECT 4, 'tom3' UNION ALL
SELECT 3, 'tom4' UNION ALL
SELECT 5, 'tom5' UNION ALL
SELECT 8, 'tom6' UNION ALL
SELECT 1, 'tom7'
IF OBJECT_ID('KJ') IS NOT NULL
DROP TABLE KJ
CREATE TABLE KJ(KeJianID VARCHAR(20), Zuozhe VARCHAR(50))
INSERT INTO KJ
SELECT 1001, 'tom1' UNION ALL
SELECT 1002, 'tom2' UNION ALL
SELECT 1003, 'tom3' UNION ALL
SELECT 1004, 'tom4' UNION ALL
SELECT 1005, 'tom5' UNION ALL
SELECT 1006, 'tom6' UNION ALL
SELECT 1007, 'tom7'
select * from UuserUPDATE Uuser
SET Ucount = Ucount - 1
WHERE
(Uname IN
(
SELECT Uname
FROM Uuser JOIN KJ ON Uuser.Uname = KJ.Zuozhe
WHERE (KJ.KeJianID IN (1004, 1002))
)
)select * from Uuser测试结果良好,没有问题。不知道你的问题出在哪。
DROP TABLE Uuser
CREATE TABLE Uuser(Ucount INT, Uname VARCHAR(50))INSERT INTO Uuser
SELECT 4, 'tom1' UNION ALL
SELECT 2, 'tom' UNION ALL
SELECT 4, 'tom3' UNION ALL
SELECT 3, 'tom4' UNION ALL
SELECT 5, 'tom5' UNION ALL
SELECT 8, 'tom6' UNION ALL
SELECT 1, 'tom7'
IF OBJECT_ID('KJ') IS NOT NULL
DROP TABLE KJ
CREATE TABLE KJ(KeJianID VARCHAR(20), Zuozhe VARCHAR(50))
INSERT INTO KJ
SELECT 1001, 'tom1' UNION ALL
SELECT 1002, 'tom' UNION ALL
SELECT 1003, 'tom3' UNION ALL
SELECT 1004, 'tom' UNION ALL
SELECT 1005, 'tom5' UNION ALL
SELECT 1006, 'tom6' UNION ALL
SELECT 1007, 'tom7'
select * from Uuser--使用游标插入
declare cur cursor
for SELECT Uname
FROM Uuser JOIN KJ ON Uuser.Uname = KJ.Zuozhe
WHERE (KJ.KeJianID IN (1004, 1002))declare @Name varchar(100)
open cur
fetch cur into @Namewhile @@fetch_status=0
begin
UPDATE Uuser SET Ucount = Ucount - 1 WHERE Uname=@Name
fetch cur into @Name
endclose cur
deallocate cur--查看
select * from Uuser
select * from KJ如果嫌这样麻烦就只有单个id的循环的执行