August 3, 2009 at 4:13 pm
I initially posted this in the wrong section.
I think I'm starting to go a bit strange with this script. 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))
So basically where everything is true in the where statement update the partnerid from 11 to 26.
Thanks for your help in advance
Kris
August 3, 2009 at 4:48 pm
I have tried rewriting your code such that I could read it easier.
Please review both the SELECT and UPDATE statements. Let us know if the SELECT statement returns what you are expecting, then IN A TEST ENVIRONMENT try the UPDATE statement if the SELECT works correctly.
Please keep us informed.
SELECT
mm.PartnerID,
new_PartnerID = '26'
FROM
dbo.MatterMaster mm
INNER JOIN dbo.PtrMgrDeptStaff pmds
ON pmds.ObjectInstID = mm.PartnerID
INNER JOIN dbo.MatterStd ms
ON ms.smtID = mm.StdMatterID
LEFT OUTER JOIN (dbo.ClientType ClientType_364
INNER JOIN dbo.Client cl
ON ClientType_364.ctpID = cl.cltType)
ON mm.objInstID = cl.objInstID
WHERE
(mm.PartnerID = 11) AND
(mm.DateClosed IS NULL) AND
(pmds.ObjectID = 4);
UPDATE dbo.MatterMaster SET
PartnerID = '26'
FROM
dbo.MatterMaster mm
INNER JOIN dbo.PtrMgrDeptStaff pmds
ON pmds.ObjectInstID = mm.PartnerID
INNER JOIN dbo.MatterStd ms
ON ms.smtID = mm.StdMatterID
LEFT OUTER JOIN (dbo.ClientType AS ClientType_364
INNER JOIN dbo.Client cl
ON ClientType_364.ctpID = cl.cltType)
ON mm.objInstID = cl.objInstID
WHERE
(mm.PartnerID = 11) AND
(mm.DateClosed IS NULL) AND
(pmds.ObjectID = 4)
August 3, 2009 at 5:30 pm
For an EXISTS clause to work, you almost always want to have a correlated subquery. If you can run the subquery alone, then it's not a correlated subquery. If it's not a correlated subquery, then you will get the same results for every row in your main query.
Your subquery returns 206 rows, which means that (a) it's not a correlated subquery, (b) the results of the EXISTS clause is true for ALL RECORDS in your main query.
If you want to distinguish between the same table in your main query and your subquery, they need to have different aliases. If you use the same alias, then the reference to the subquery will override the reference to the main query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2009 at 5:43 pm
One other thing. You do not need the TOP 100 PERCENT. It does absolutely nothing for you and might cause a performance hit.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2009 at 7:01 pm
I'll chime in as well.
IF EXISTS returns TRUE if any row would be returned from the subquery.
It looks like you confused it with IN used with a subquery, as shown below.
select * from syscolumns
where id IN (select id -- this subquery returns a list of ids
from sysobjects
where name in ('sysrowsetcolumns','sysrowsets'))
order by id, name
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 3, 2009 at 9:32 pm
Having found out you had double posted this request for help. I redirected everyone to this thread. Unfortunately, I failed to notice that one thread was in a SQL Server 2000 forum and this one is in a SQL Server 2005 forum.
This now begs the question, what version of SQL Server are we dealing with here? If SQL Server 2005, then this is the correct forum and theard. If SQL Server 2000, then we may also have to request that this thread be moved to a SQL Server 2000 forum as i have also requested that the moderators close the other thread so that people only post in this one to keep all the answers together from this point forward.
August 3, 2009 at 9:34 pm
Thanks for that Lynn, that worked a treat. I only had to make one small adjustment but the select and the update statement did exactly what I wanted. This is what I ended up with:
SELECTmm.PartnerID,
pmds.Description AS Partner,
mm.Desc
FROMdbo.MatterMaster AS mm INNER JOIN
dbo.PtrMgrDeptStaff AS pmds ON pmds.ObjectInstID = mm.PartnerID INNER JOIN
dbo.ocsMatterStd AS ms ON ms.smtID = mm.StdMatterID LEFT OUTER JOIN
dbo.ClientType AS ClientType_364 INNER JOIN
dbo.Client AS cl ON ClientType_364.ctpID = cl.cltType ON mm.InstID = cl.InstID
WHERE (mm.PartnerID = 11) AND
(mm.DateClosed IS NULL) AND
(pmds.ObjectID = 4);
Kris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply