Solved

SQL Server Scripting - Iterate through a number of records and then alter another table

Posted on 2014-07-17
3
1,084 Views
Last Modified: 2014-07-17
Greetings,
I am working on an SQL Server script to automate a data cleansing routine as follows:
I need to select records in table ABC(20 rows returned)

For each record(20) that is returned I need to take a value from one of the columns in ABC(Ex: ABC.MY_COLUMN) and then use it to make an update in table XYZ (Ex: XYZ.MY_COLUMN).  In other words I need to take the value from ABC.MY_COULMN  and update the XYZ table with that value.

Many Thanks
0
Comment
Question by:gNome
[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 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40203296
Assuming there is a field you can join the two tables on, use UPDATE.  For example:

UPDATE XYZ
SET MY_COLUMN = a.MY_COLUMN
FROM XYZ x INNER JOIN
    ABC a ON x.ID = a.ID
WHERE a.MY_DATE >= '2014-07-01'

Open in new window

0
 
LVL 13

Accepted Solution

by:
Russell Fox earned 500 total points
ID: 40203302
The trick is that you shouldn't think about iterating over those records: that's how programmers think, not database developers. Instead of thinking about editing 20 records, think about editing one column:
UPDATE t1
SET MY_COLUMN = t2.MY_COLUMN
FROM XYZ t1
INNER JOIN (
    SELECT ID, MY_COLUMN
    FROM ABC
    WHERE StuffToGetToThose20Records
    ) t2
    ON t1.ID = t2.ID

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40203561
:) yep, change of thinking from loops/iterations to thinking joins seems needed.
tongue-in-cheek "translation" follows:

I am working on an SQL Server script
I am working on a query

to automate a data cleansing routine as follows:
to facilitate data cleansing

I need to select records in table ABC(20 rows returned)
I need a where clause or subquery to select from table ABC(n rows in resultset)

For each record(20) that is returned
For each match of these rows to another table; XYZ

I need to take a value from one of the columns in ABC(Ex: ABC.MY_COLUMN) and then use it to make an update in table XYZ (Ex: XYZ.MY_COLUMN).
I need to update a column of XYZ

In other words I need to take the value from ABC.MY_COLUMN  and update the XYZ table with that value.
In short I need an update query


In the unlikely event: no points please
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question