Solved

Need an UPDATE statement that update's one row of a table based on another row in that same table.

Posted on 2013-01-30
3
408 Views
Last Modified: 2013-01-30
I've been given this table with over 70 fields in it (don't ask)..  Anyway, I've added a new row to it for testing purposes which is full of test data (It's like a template-row I guess you'd call it).  Now I'd like to use that row to spin-off copies of, into the same table (while just changing the primaryID).  This will allow me to test and test with fresh new test rows without having to create new ones manually....Uhhhhg.  Currently, I've got the fresh template-row done, and have entered a new row with a new ID (and the rest of the fields are NULL).  How can I update this null-row with data from the clean template-row?

I was hoping to do something like this:
UPDATE Table t1 SET t1.col1, t1.col2,...t1.col70
SELECT t2.col1, t2.col2,...t2.col70 FROM Table t2 WHERE id = myTemplateRowID
WHERE t1.id = myNullRowID

Open in new window

I thought I saw this sort of Update statement somewhere once (syntax isn't correct I know).
0
Comment
Question by:David L. Hansen
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 38837100
Yep, you're pretty close:


UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, ...t1.col70 = ...
FROM dbo.table t1
INNER JOIN dbo.table t2 ON
    t2.id = myTemplateRowID AND
    t1.id = myNullRowID
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 38837208
Perfect...thank you.
0
 
LVL 15

Author Closing Comment

by:David L. Hansen
ID: 38837209
sweet!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now