Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
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 1 day ago
#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 1 day ago
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 …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data. Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month10 days, 5 hours left to enroll
609 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.