Copy down a value from a parent tuple in SQL

Question

I am looking for an elegant way to copy down a value from a parent tuple to a child. I need it to be copied as it can change independently of the parent value, but that is the baseline to start.

Here is a simplified version of the table:

CREATE TABLE Parent (
    Id INT NOT NULL PRIMARY KEY,
    ParentValue VARCHAR(128)
);


CREATE TABLE Child (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL FOREIGN KEY REFERENCES Parent(Id),
    ChildValue VARCHAR(128)
);

Now what I am looking to do is this:

   UPDATE Child
   SET ChildValue = Parent.ParentValue
   WHERE TRUE

Does that makes sense? I have a looping way, but it does not seem efficient for the multitude of records I plan on running it for.

Possible duplicate of [SQL Server : copy data from one table to another](http://stackoverflow.com/questions/13077746/sql-server-copy-data-from-one-table-to-another) — Pondlife, Dec 18 '12 at 21:51

score 2 · Accepted Answer · answered Dec 18 '12 at 21:49

You can use the proprietary UPDATE FROM syntax

UPDATE Child
SET ChildValue = ParentValue
FROM Child
JOIN Parent ON Parent.Id = Child.ParentId;

or the standard SQL MERGE syntax as you are on 2008

MERGE INTO Child
   USING Parent
      ON Parent.Id = Child.ParentId
WHEN MATCHED THEN
   UPDATE 
      SET ChildValue = ParentValue;

score 1 · Answer 2 · answered Dec 18 '12 at 21:53

1

A portable way to write the query is using a correlated subquery in the set clause:

update child
    set childValue = (select ParentValue from Parent where Parent.ParentId = Child.ParentId)

I don't know if you consider this elegant, though.

answered Dec 18 '12 at 21:53

Gordon Linoff

1,242,037
58
646
786

Copy down a value from a parent tuple in SQL

2 Answers2