Update selected records - noob

Question

Hi, Sorry this is a bit of a noob question. I have the following select statement. I would like to update the values of ContactAssociate from 'Bob' to 'Jane'. Is it possible to do this?

SELECT TOP (1500) ContactID, ContactNotes, ContactAssociate, ContactAppointment
FROM  tb_Contact
WHERE (ContactAssociate = 'Bob') AND (ContactAppointment = 0)

Any help appreciated.

Answer 1

UPDATE tb_Contact SET ContactAssociate='Jane'
 WHERE ContactID IN
      (
           SELECT TOP (1500) ContactID
           FROM  tb_Contact
           WHERE (ContactAssociate = 'Bob') AND (ContactAppointment = 0)
      )

• For general tutorial on UPDATE see here.
• For a specific reference for MSSQL see here.

Answer 2

Use:

UPDATE TB_CONTACT
   SET contactassociate = 'Jane'
 WHERE contactassociate = 'Bob'

The query you supplied checked for contactappointment being zero - if you wanted to include that check, the query would be:

UPDATE TB_CONTACT
   SET contactassociate = 'Jane'
 WHERE contactassociate = 'Bob'
   AND contactappointment = 0

The UPDATE statement doesn't support the TOP keyword, so your update statement would be:

UPDATE TB_CONTACT
   SET contactassociate = 'Jane'
 WHERE contactid IN (SELECT TOP (1500)
                            contactid 
                       FROM TB_CONTACT
                      WHERE contactassociate = 'Bob'
                        AND contactappointment = 0)

...but that will give random contacts - you should specify an ORDER BY clause in the subquery to get consistent results.

Reference:

• UPDATE