Insert Data from same table but change one value

Posted on 2011-10-13
Medium Priority
Last Modified: 2012-05-12

I've a simple table:
Val1 Val2 Val3
1      1       ...
1      2       ...
2      1       ...
2      2       ...

Now I need somethin like this but in one step:
INSERT INTO mytable SELECT * FROM mytable WHERE Val1=1
UPDATE mytable SET Val1=3

The original Val1=1 should be unchanged, only the new inserted ones should be changed to 3

The result I need:
Val1 Val2 Val3
1      1       ...
1      2       ...
2      1       ...
2      2       ...
3      1       ...
3      2       ...

There's a index of Val1 + Val2 if it's important.


Question by:andre72
LVL 70

Accepted Solution

Éric Moreau earned 2000 total points
ID: 36962011
change the value at the same time you are inserting:

INSERT INTO mytable (Val1, Val2, Val3)
SELECT 3, Val2, Val3
FROM mytable
WHERE Val1=1

Author Closing Comment

ID: 36962080

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

In this article I will describe the Detach & Attach 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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

839 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