Insert Data from same table but change one value

Posted on 2011-10-13
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 69

    Accepted Solution

    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


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    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.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    746 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

    13 Experts available now in Live!

    Get 1:1 Help Now