SQL-Server: Updating table from another table

Question

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 IDs 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 SecondaryIDs 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

DVJex · Accepted Answer · 2016-08-18 11:30:25Z

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]

Explanation:

[Redaction] red [LinkedRedactions] redLnk [Redaction] redPrimary

red, redLnk and redPrimary are called aliases and they're used to call the table by using a different name.

INNER JOIN

This is a type of join that only matches if the same column value exists on both the left and the right table.

UPDATE red --SET statement FROM [Redaction] red

This updates only the [Redaction] table via it's alias 'red'.

INNER JOIN [LinkedRedactions] redLnk ON red.[ID] = redLnk.SecondaryIDs

This joins the Link table and the table to be updated by the secondary IDs and ID respectively.

INNER JOIN [Redaction] redPrimary ON redLnk.PrimaryID = redPrimary.[ID]

This joins the Link table and the [Redaction] table again but uses the Primary ID and ID columns respectively. This is a self join which allows us to update a set of values in a table with a different set of values from the same table.

No WHERE conditions are needed since the conditions are handled in the ON clauses.

Thanks, works perfectly. Could you give a brief explanation of how it works please?

neer · Accepted Answer · 2016-08-18 11:05:38Z

You can use UPDATE FROM

UPDATE [Redaction] SET [X] = T.[X], [Y] = T.[Y] FROM @T T WHERE [Redaction].[ID] = T.[ID]

Collectives™ on Stack Overflow

SQL-Server: Updating table from another table

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related