Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-07-17
3
Medium Priority
?
1,100 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 93

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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

664 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