JChristen JChristen - 3 months ago 11
SQL Question

SQL-Server: Updating table from another table

I have two tables, one which represents some data and one that links two pieces of data together.
The first,

Redaction
, has three columns;
ID
,
X
,
Y
.
The second,
LinkedRedactions
, has two columns;
PrimaryID
,
SecondaryID
, which are the
ID
s of two of the rows from
Redaction
that are linked, and need to have the same
X
and
Y
value.

What I want to do is update the values of
X
and
Y
in
Redaction
for the
SecondaryID
s if they are not already the same as the values for
X
and
Y
for the corresponding
PrimaryID
.

Unfortunately I cannot use a
TRIGGER
since the scripts will be running on kCura's Relativity platform, which doesn't allow them. A SQL script would be ideal, which would be run every few seconds by an agent.

I've tried declaring a temporary table and updating from that, but that gives me the error


"must declare scalar variable @T"


DECLARE @T TABLE (
[ID] INT, [X] INT, [Y] INT
)

INSERT INTO @T
SELECT
[ID], [X], [Y]
FROM
[Redaction] AS R
WHERE
[ID] IN (
SELECT [PrimaryID] FROM [LinkedRedactions]
)

UPDATE
[Redaction]
SET
[X] = @T.[X], [Y] = @T.[Y]
WHERE
[Redaction].[ID] IN (
SELECT [ID] FROM @T
)


Disclaimer: This is only my second day of SQL, so more descriptive answers would be appreciated

Answer

The entire code can be simplified using inner joins.

UPDATE red
SET [X] = redPrimary.[X], [Y] = redPrimary.[Y]
FROM [Redaction] red
INNER JOIN [LinkedRedactions] redLnk ON red.[ID] = redLnk.SecondaryIDs
INNER JOIN [Redaction] redPrimary ON redLnk.PrimaryID = redPrimary.[ID]