Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.
COURSE of the MONTH
9 days, 21 hours 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
#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
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
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:
Each Login (Windows or SQL)
* Its Server Roles
* Every database to which the login is mapped
* The associated "Database User" for this …
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month9 days, 21 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.