Update Table using JOIN and Inside Query

Question

I have imported a Members table which has invalid States. Instead of having the State Abbreviation, it contains the completed State Name.

The below query does work and pulls up the Members.StateName and State.Abbreviation :

Connecticut CT
Maryland    MD
Massachusetts   MA

Now I am trying to write and Update command where I am replacing the Members.StateName with the State.Abbreviation .

UPDATE dbo.Members
SET memState = (SELECT s.stAbv FROM dbo.State AS s
JOIN Members AS m ON s.stName = m.memState )
WHERE memState NOT IN (SELECT s.stAbv FROM dbo.State AS s)

ERROR: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated. I understand the error says my inside query returns multiple rows, so it can not Update. How do I get this to work?

Do you have more than one state row in dbo.State with the same name? If you do - shouldn't one or other be deleted / flagged as deleted - or tied to a particular country, if you've US states, UK counties, etc. in the table? — Will A
– Will A, Commented May 7, 2011 at 16:21
The below code worked UPDATE dbo.Members SET memState = (SELECT TOP 1 s.stAbv FROM dbo.State AS s JOIN Members AS m ON s.stName = m.memState ) WHERE memState NOT IN (SELECT s.stAbv FROM dbo.State AS s) — Ravi Ram
– Ravi Ram, Commented May 7, 2011 at 20:44
Are you sure that's what you're after though? Will the abbreviation returned by a TOP 1 be the abbreviation for 'the right' state? Why'd you have > 1 state with a particular name in your table? — Will A
– Will A, Commented May 8, 2011 at 22:59

Andriy M · Accepted Answer · 2011-05-07 21:45:03Z

2

I think you could do it like this:

UPDATE m
SET memState = s.stAbv
FROM dbo.Members AS m
  INNER JOIN dbo.State AS s ON s.stName = m.memState

Not entirely sure about your WHERE clause, but most probably it's not needed.

answered May 7, 2011 at 21:45

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ceving · Accepted Answer · 2011-05-07 16:36:29Z

0

You can not assign more than 1 value to memState. Try SELECT DISTINCT to join duplicate values to one value.

answered May 7, 2011 at 16:36

ceving

24.1k15 gold badges120 silver badges201 bronze badges

Collectives™ on Stack Overflow

Update Table using JOIN and Inside Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related