Copy Data from One Existing Row to Another Existing Row in SQL

Copy data from one existing row to another existing row in SQL?

Maybe I read the problem wrong, but I believe you already have inserted the course 11 records and simply need to update those that meet the criteria you listed with course 6's data.

If this is the case, you'll want to use an UPDATE...FROM statement:

UPDATE MyTable
SET
    complete = 1,
    complete_date = newdata.complete_date,
    post_score = newdata.post_score
FROM
    (
    SELECT
        userID,
        complete_date,
        post_score
    FROM MyTable
    WHERE
        courseID = 6
        AND complete = 1
        AND complete_date > '8/1/2008'
    ) newdata
WHERE
    CourseID = 11
    AND userID = newdata.userID

See this related SO question for more info

How to copy a row and insert in same table with a autoincrement field in MySQL?

Use INSERT ... SELECT:

insert into your_table (c1, c2, ...)
select c1, c2, ...
from your_table
where id = 1

where c1, c2, ... are all the columns except id. If you want to explicitly insert with an id of 2 then include that in your INSERT column list and your SELECT:

insert into your_table (id, c1, c2, ...)
select 2, c1, c2, ...
from your_table
where id = 1

You'll have to take care of a possible duplicate id of 2 in the second case of course.

Insert new rows into table but copy data from another row in the table

Try something like this:

INSERT INTO your_table (grd, gsd, Name, Location)
    SELECT
        grd,--or change it here as you need (for example replacing with value 113)
        gsd,
        Name,
        Location
    FROM your_table
    --WHERE some condition (e.g. grd = 112)

Copy rows from the same table and update the ID column

INSERT INTO ProductDefinition (ProdID, Definition, Desc)
SELECT
  xxx, Definition, Desc
FROM
  ProductDefinition
WHERE
  ProdID = yyy

The xxx is your new ProdID and the yyy is your old one. This also assumes that DefID is automagically populated on INSERT.

How to copy a row from one SQL Server table to another

As long as there are no identity columns you can just

INSERT INTO TableNew
SELECT * FROM TableOld
WHERE [Conditions]

---

Related Topics