Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.
Course Of The Month
5 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL UPDATE
Posted on 2013-01-04
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.
update #State
SET StateName = StateProvinceDesc
from StateProvince
WHERE StateCode = StateProvinceCode
Not sure what the problem is.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
-
2
5 Comments
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
Update s
SET s.StateName = sp.StateProvinceDesc
FROM #State s
INNER JOIN StateProvince sp
ON s.StateCode = sp.StateProvinceCode
Active today
#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.
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.
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
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
Active today
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Course of the Month5 days, 1 hour left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.