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

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
?
418 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
[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
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

721 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