Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
421 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

972 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