CODEWITHSUNDEEP
sqlsql-servert-sql
Chin
Chin

Reputation: 12712

How to update value in a table

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)

Upvotes: 1

Views: 658

Answers (2)

Amirshk
Amirshk

Reputation: 8258

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.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332531

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:

Upvotes: 5

Related Questions