July 16, 2009 at 1:48 pm
Okay I'm stumped. I need to update a value in the same table but different record based on the UserID column. I have about 35,000 records to update based on this criteria:
So if UserID = 33138 and PropertyID = 7 and PropertyValue = False
Update UserID 33138 Set PropertyValue = True where PropertyID = 9
UserID PropertyID PropertyValue
331389 True
331388 False
331385 False
331387 False
331309 True
331308 False
331305 False
331307 False
Thanks.
July 16, 2009 at 1:57 pm
So if UserID = 33138 and PropertyID = 7 and PropertyValue = False
Update UserID 33138 Set PropertyValue = True where PropertyID = 9
Are you trying to make us fool or I am being full here.?
"So if UserID = 33138 and PropertyID = 7 and PropertyValue = False"
this one says when
UserID = 33138 and
PropertyID = 7 and
PropertyValue = False You wnat to update that row.
"Update UserID 33138 Set PropertyValue = True where PropertyID = 9"
This one says you want to update all UserID 33138 to set Propertyvale = true
where PropertyID = 9
This two doesn't make sense.
May be u wanna calrify more. Even post the result sets u desired to have.
July 16, 2009 at 2:06 pm
Rich96 (7/16/2009)
Okay I'm stumped. I need to update a value in the same table but different record based on the UserID column. I have about 35,000 records to update based on this criteria:So if UserID = 33138 and PropertyID = 7 and PropertyValue = False
Update UserID 33138 Set PropertyValue = True where PropertyID = 9
UserID PropertyID PropertyValue
331389 True
331388 False
331385 False
331387 False
331309 True
331308 False
331305 False
331307 False
Thanks.
No fooling here.. just confusion.
Original data set (I updated UserID 33130 PropertyID:7, PropertyValue:True from original post):
UserID PropertyID PropertyValue
331389 True
331388 False
331385 False
331387 False
331309 True
331308 False
331305 False
331307 True
Desired data set:
UserID PropertyID PropertyValue
331389 False
331388 False
331385 False
331387 False
331309 True
331308 False
331305 False
331307 True
Since UserID 33138 has a record with PropertyID 7 and the PropertyValue is False, I then want to update the same UserID but different record with a PropertyID 9 and set the PropertyValue to False.
Make sense???
July 16, 2009 at 2:15 pm
UserID PropertyID PropertyValue
33138 9 True
33138 8 False
33138 5 False
33138 7 False
33130 9 True
33130 8 False
33130 5 False
33130 7 True
Desired data set:
UserID PropertyID PropertyValue
33138 9 False
33138 8 False
33138 5 False
33138 7 False
33130 9 True
33130 8 False
33130 5 False
33130 7 True
Looks like ur desired dataset is something other than u intended to have.
If it is the real dataset u want to have a simple update will do it
Update tablename
set PropertyValue = 'False'
where UserID = 33138
**********SORRY IGNORE THIS POST I GOT WHAT U TRYING TO DO******************
July 16, 2009 at 2:32 pm
Well there are 166,443 rows that will need to be updated based on PropertyID equaling 7 and the PropertyValue equaling False.
July 16, 2009 at 2:36 pm
Here this should work.........
CREATE TABLE #TEST ([UId] int, PId int, PV varchar(10) )
INSERT INTO #TEST VALUES
(33138, 9, 'True'),
(33138, 8, 'False'),
(33138, 5, 'False'),
(33138, 7, 'False'),
(33130, 9, 'True'),
(33130, 8, 'False'),
(33130, 5, 'False'),
(33130, 7, 'True')
SELECT * FROM #TEST
UPDATE A
SET A.PV = 'FALSE'
FROM #TEST A
INNER JOIN
(
SELECT [Uid] FROM #TEST
WHERE PId = 7 and PV = 'False'
GROUP BY [Uid]
) B
ON A.UId = B.UId
AND A.PV 'False'
July 16, 2009 at 3:11 pm
Gkhadka (7/16/2009)
Here this should work.........CREATE TABLE #TEST ([UId] int, PId int, PV varchar(10) )
INSERT INTO #TEST VALUES
(33138, 9, 'True'),
(33138, 8, 'False'),
(33138, 5, 'False'),
(33138, 7, 'False'),
(33130, 9, 'True'),
(33130, 8, 'False'),
(33130, 5, 'False'),
(33130, 7, 'True')
SELECT * FROM #TEST
UPDATE A
SET A.PV = 'FALSE'
FROM #TEST A
INNER JOIN
(
SELECT [Uid] FROM #TEST
WHERE PId = 7 and PV = 'False'
GROUP BY [Uid]
) B
ON A.UId = B.UId
AND A.PV 'False'
If I add more records it doesn't update just the PropertyID 9.
INSERT INTO #TEST VALUES
(33138, 9, 'True')
INSERT INTO #TEST VALUES
(33138, 8, 'False')
INSERT INTO #TEST VALUES
(21000, 23, 'True')
INSERT INTO #TEST VALUES
(33138, 5, 'False')
INSERT INTO #TEST VALUES
(33138, 7, 'False')
INSERT INTO #TEST VALUES
(33130, 35, 'False')
INSERT INTO #TEST VALUES
(33130, 8, 'False')
INSERT INTO #TEST VALUES
(33130, 5, 'False')
INSERT INTO #TEST VALUES
(33130, 7, 'False')
INSERT INTO #TEST VALUES
(33130, 9, 'True')
INSERT INTO #TEST VALUES
(33130, 23, 'True')
July 16, 2009 at 3:23 pm
SO u just want to update where Propertyid is 9. I thought u want to update all other records with that UserID.....
here this should do
UPDATE A
SET A.PV = 'FALSE'
-- use this select to see ur results set
--SELECT *
FROM #TEST A
INNER JOIN
(
SELECT [Uid] FROM #TEST
WHERE PId = 7 and PV = 'False'
GROUP BY [Uid]
) B
ON A.UId = B.UId
AND A.PId = 9 -- i just change this part. now it will just update records with property id 9
July 16, 2009 at 3:29 pm
haha.. it's like pulling teeth from me huh?? thank you so much for your patience Gkhadka! I greatly appreciate it.
July 16, 2009 at 3:35 pm
Glad that I was able to do it....and I understand too,been that road so many times.....
July 16, 2009 at 3:48 pm
is this what you're after:
DROP TABLE [#TEST]
CREATE TABLE #TEST ([UId] int, PId int, PV varchar(10) )
INSERT INTO #TEST VALUES(33138, 9, 'True')
INSERT INTO #TEST VALUES(33138, 8, 'False')
INSERT INTO #TEST VALUES(33138, 5, 'False')
INSERT INTO #TEST VALUES(33138, 7, 'False')
INSERT INTO #TEST VALUES(33130, 9, 'True')
INSERT INTO #TEST VALUES(33130, 8, 'False')
INSERT INTO #TEST VALUES(33130, 5, 'False')
INSERT INTO #TEST VALUES(33130, 7, 'True')
INSERT INTO #TEST VALUES(33138, 9, 'True')
INSERT INTO #TEST VALUES(33138, 8, 'False')
INSERT INTO #TEST VALUES(21000, 23, 'True')
INSERT INTO #TEST VALUES(33138, 5, 'False')
INSERT INTO #TEST VALUES(33138, 7, 'False')
INSERT INTO #TEST VALUES(33130, 35, 'False')
INSERT INTO #TEST VALUES(33130, 8, 'False')
INSERT INTO #TEST VALUES(33130, 5, 'False')
INSERT INTO #TEST VALUES(33130, 7, 'False')
INSERT INTO #TEST VALUES(33130, 9, 'True')
INSERT INTO #TEST VALUES(33130, 23, 'True')
--SELECT*
UPDATEtt
SETtt.[PV] = 'False'
FROM[#TEST] tt
INNER JOIN (SELECTDISTINCT [UId]
FROM#TEST
WHERE[PV] = 'False'
AND[PId] = 7) tON tt.[UId] = t.[UId]
WHEREtt.[PId] = 9
edit: guess i was a bit late
[font="Arial Narrow"]bc[/font]
July 16, 2009 at 3:56 pm
Here is my final script:
UPDATE A
SET A.PropertyValue = 'False'
FROM UserPropertyData A
INNER JOIN
(
SELECT c.UserID FROM UserPropertyData C
INNER JOIN
UserList d
ON c.UserID = d.UserID
WHERE d.IsCompany = '1' and c.PropertyID = 7 and c.PropertyValue = 'False'
GROUP BY c.UserID
) B
ON A.UserID = B.UserID
AND A.PropertyID = 9
I added another inner join to check another table column and it produces the desired results. Thanks for all the feedback.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply