August 2, 2009 at 8:14 pm
I think I'm starting to go a bit strange with this script or maybe Mondayitis. I'm trying to update one column within a select statement. When I run the select statement I get 206 results which is right when I put the update statement first it updates over 96000 records. I also get just null displayed as the sortname for some reason.
UPDATE dbo.MatterMaster SET PartnerID = '26' WHERE EXISTS
(SELECT TOP (100) PERCENT dbo.Client.cltSortName AS SortName, dbo.MatterStd.smtDesc AS Std_Mtr_73,
dbo.PtrMgrDeptStaff.pmdDescription
FROM dbo.PtrMgrDeptStaff INNER JOIN
dbo.MatterStd INNER JOIN
dbo.MatterMaster ON dbo.MatterStd.smtID = dbo.MatterMaster.StdMatterID ON
dbo.PtrMgrDeptStaff.ObjectInstID = dbo.MatterMaster.PartnerID LEFT OUTER JOIN
dbo.ClientType AS ClientType_364 INNER JOIN
dbo.Client ON ClientType_364.ctpID = dbo.Client.cltType ON dbo.MatterMaster.objInstID = dbo.Client.objInstID
WHERE (dbo.MatterMaster.PartnerID = 11) AND (dbo.MatterMaster.DateClosed IS NULL) AND
(dbo.PtrMgrDeptStaff.ObjectID = 4))
Thanks for your help in advance
Kris
August 2, 2009 at 8:52 pm
From what I can see, there's no correlation between the UPDATE and the SELECT. How do the columns in the SELECT list relate to the table being updated?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2009 at 9:20 pm
Where the partnerid = 11 I want to change it to 26 assuming all the other conditions in the where statement are true as well.
Kris
August 2, 2009 at 9:36 pm
Where the partnerid = 11 I want to change it to 26 assuming all the other conditions in the where statement are true as well.
Kris
August 3, 2009 at 8:42 pm
There's one curiousity about your code you'll need to resolve, and it's associated with the data type of the PartnerID column of dbo.MatterMaster. You want to set it to a string value in the SET statement, but are testing it for an integer value in the query. I'm providing code that corrects both instances to use integer values, so if you need a string value instead, just add single quotes. I believe I have this right, but it would be most useful if Mr. Moden could validate I have this UPDATE statement correct. The other things I did to the query were to remove the TOP 100 PERCENT. That appears to me to serve no purpose here. Also, the fields you are selecting in the query aren't used in any way, so they didn't need to be there, whereas the fields you're testing for values do need to be there, but tested in a different part of the overall query.
Here's the suggested code:
UPDATE dbo.MatterMaster
SET PartnerID = 26
FROM (
SELECT dbo.MatterMaster.PartnerID AS PID, dbo.MatterMaster.DateClosed AS DC, dbo.PtrMgrDeptStaff.ObjectID AS OID
FROM dbo.PtrMgrDeptStaff
INNER JOIN dbo.MatterStd
INNER JOIN dbo.MatterMaster
ON dbo.MatterStd.smtID = dbo.MatterMaster.StdMatterID
ON dbo.PtrMgrDeptStaff.ObjectInstID = dbo.MatterMaster.PartnerID
LEFT OUTER JOIN dbo.ClientType AS ClientType_364
INNER JOIN dbo.Client
ON ClientType_364.ctpID = dbo.Client.cltType
ON dbo.MatterMaster.objInstID = dbo.Client.objInstID
) AS X
WHERE X.PID = 11
AND X.DC IS NULL
AND X.OID = 4
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 3, 2009 at 9:21 pm
Curious why you have double posted this request for help? You now have two threads going with different people answering the same question and those in one thread not knowning what is happening in the other.
Please do not double post. Please post further replies here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply