CODEWITHSUNDEEP
sql-server
RogueSpear00
RogueSpear00

Reputation: 619

Update SQL Records Based on Joined Select

This seems really straight forward, and I think I just want to validate and ensure that I'm doing this correctly.

Basically I want to run an UPDATE on these records:

SELECT     contact.company, contact.coaddress, contact.costate, contact.cocity, contact.cozip, contact.phonebusiness, dial.AgentID, dial.Attempts, dial.CRC
FROM         contact LEFT OUTER JOIN
                  dial ON contact.DialID = dial.DialID
WHERE     (dial.AgentID > '-1') AND (contact.cozip LIKE '600%' OR
                  contact.cozip LIKE '601%' OR
                  contact.cozip LIKE '606%' OR
                  contact.cozip LIKE '608%' OR
                  contact.cozip LIKE '605%' OR
                  contact.cozip LIKE '604%' OR
                  contact.cozip LIKE '613%' OR
                  contact.cozip LIKE '611%')

I get back my correct results on the SELECT statement. I need to now UPDATE those records:

UPDATE    dial
SET              ProjectID = '60'
FROM         dial INNER JOIN
                  contact ON dial.DialID = contact.DialID
WHERE     (dial.AgentID > '-1') AND (contact.cozip LIKE '600%' OR
                  contact.cozip LIKE '601%' OR
                  contact.cozip LIKE '606%' OR
                  contact.cozip LIKE '608%' OR
                  contact.cozip LIKE '605%' OR
                  contact.cozip LIKE '604%' OR
                  contact.cozip LIKE '613%' OR
                  contact.cozip LIKE '611%')

Does this look correct? Is there a more efficient way of doing this?

Upvotes: 2

Views: 330

Answers (2)

gbn
gbn

Reputation: 432421

Yes, this looks correct.

There are some LIKE tricks and I like aliases personally. The alias d makes it clear which table is being updated in the JOIN. The [] in the LIKE means a range or set

UPDATE
    d
SET 
    ProjectID = '60'
FROM
    dial d
    INNER JOIN
    contact ON d.DialID = contact.DialID
WHERE
   (d.AgentID > -1) --edit, int. Not varchar
   AND 
   (contact.cozip LIKE '60[016854]%' OR contact.cozip LIKE '61[13]%')

Should AgentID be the number -1 too?

Upvotes: 4

Nathanial Woolls
Nathanial Woolls

Reputation: 5291

That looks like the correct syntax to me for Deleting or Updating based on a JOIN.

Note that you could alternately use a subquery and the IN operator, e.g. "UPDATE dial SET ProjectID = '60' WHERE DialID in (SELECT DialID from dial...)".

Upvotes: 1

Related Questions