SQL UPDATE

Below code is only updating 1 record. Should be updating the 50 records in the temp table.

update #State
SET StateName = StateProvinceDesc
from StateProvince
WHERE  StateCode = StateProvinceCode

Not sure what the problem is.
thayduckProgrammer AnalystAsked:
Who is Participating?
 
tim_csConnect With a Mentor Commented:
What are the datatypes for the fields, do they match?  Is it always just Alabama that gets updated?

What happens if you run this one?  

Update s
SET s.StateName = sp.StateProvinceDesc
FROM #State s
   INNER JOIN StateProvince sp
      ON s.StateCode = sp.StateProvinceCode
      AND s.StateName <> sp.StateProvinceDesc
0
 
tim_csCommented:
Are you sure there are 50 records that match?  Try writing it this way.

Update s
SET s.StateName = sp.StateProvinceDesc
FROM #State s
   INNER JOIN StateProvince sp
      ON s.StateCode = sp.StateProvinceCode
0
 
thayduckProgrammer AnalystAuthor Commented:
#State
AL      USA      AL-USA      Alabama
AR      USA      AR-USA      AR-USA
AZ      USA      AZ-USA      AZ-USA

StateProvince
2      AK         Alaska              USA
1      AL         Alabama              USA
3      AR         Arkansas              USA
4      AZ         Arizona              USA
5      CA         California         USA
6      CO         Colorado              USA
7      CT         Connecticut      USA

You get same results as me, 1 record updated, Alabama.
0
 
thayduckProgrammer AnalystAuthor Commented:
I am re-keying the State Province records into the StateProvince table.
I re-keyed 4 records and reran query. Now those records are being updated correctly.
Visually they all look right, so I am not sure why re-keying over the state code in the table is  causing the records to now be updateable. ???????????????????????????????????????????????

I will let you know if this is all fixed after I re-key all records.
0
 
thayduckProgrammer AnalystAuthor Commented:
Re-Keyed records and everything is fine now.

Last time I do a copy and paste when creating records in a table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here