Solved

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

Posted on 2014-07-17
3
1,093 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 49

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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