Will this SQL update the records from this select? 1

[dndaughtery]

I have been able to select the records I need to update using the following Select statement:


Select *
From Rejects inner join Card on Rejects.CardID = Card.CardID
where RIGHT(Card.AccountNumber,6) in (SELECT RIGHT(RWMSMO,6)from Table1)AND Rejects.SentToCollections is null And Rejects.Collection = -1 And rejectamount in (SELECT RWCKAMT From Table1)
Group By RIGHT(Card.AccountNumber,6), Rejects.rejectID
Order By RIGHT(Card.AccountNumber,6) DESC


Will the following update update the records from the previous select statement?

Update Rejects
Set SentToCollections = GetDate()-1
From Rejects inner join Card on Rejects.CardID = Card.CardID
where RIGHT(Card.AccountNumber,6) in (SELECT RIGHT(RWMSMO,6)from Table1)AND Rejects.SentToCollections is null And Rejects.Collection = -1 And rejectamount in (SELECT RWCKAMT From Table1)
Group By RIGHT(Card.AccountNumber,6), Rejects.rejectID
Order By RIGHT(Card.AccountNumber,6) DESC


Also, what's the SQL Statement to rollback the update if it messes up everything?

 

[dndaughtery]

Oops the correct Select was




SELECT Count(*)
From Rejects inner join Card on Rejects.CardID = Card.CardID
where RIGHT(Card.AccountNumber,6) in (SELECT RIGHT(RWMSMO,6)from Table1)AND Rejects.SentToCollections is null And Rejects.Collection = -1 And rejectamount in (SELECT RWCKAMT From Table1)
Group By RIGHT(Card.AccountNumber,6), Rejects.rejectID
Order By RIGHT(Card.AccountNumber,6) DESC

 

[SQLSister]

Let me show you a trick we use to check to see if what were doing in an update will select the right records to update

Update PP
Set first_name = dbo.fn_ProperCase(sh.FirstName)
--Select   P.First_Name, dbo.fn_ProperCase(sh.FirstName)
From People P join Client_Identifiers S On p.pp_id = s.pp_id and s.client_identifier_type_id = 775
join Targets sh on s.client_identifier = sh.physicianno
Where P.First_Name <> sh.firstname

Then you run the select statement first to see what records it selects by highlighting from the end of the comment marker to the end of the statement. When you are sure you are getting the data you want, run the update. Put wahtever columns you feel you need to see to tell if the statment is selecting the right records and I usuallly toss in the transformation I'm going to use inthe update if it is complex to make sure that what I am changing the data to is what I want.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[dndaughtery]

Like this? The select returned the correct records.



Update Rejects
Set SentToCollections = getdate() - 1
--SELECT Count(Rejects.rejectID), Rejects.SentToCollections
From Rejects inner join Card on Rejects.CardID = Card.CardID
where RIGHT(Card.AccountNumber,6) in (SELECT RIGHT(RWMSMO,6)from Table1)AND Rejects.SentToCollections is null And Rejects.Collection = -1 And rejectamount in (SELECT RWCKAMT From Table1)
Group By RIGHT(Card.AccountNumber,6), Rejects.rejectID, Rejects.SentToCollections
Order By RIGHT(Card.AccountNumber,6) DESC

 

[dndaughtery]

WHEW! worked great