CODEWITHSUNDEEP
sql-servert-sqlcopy
shokked
shokked

Reputation: 23

Copy column values from one table to another

I'm trying to create a T-SQL query for several hours now. Google and the forum search did not help, so I hope someone can help me with this.

There are two tables, Change and Journal. Each row in Change has 5 related rows in Journal.

What I want to do is to copy the column value (CreatedDateTime) of a specific related row from Journal into the column AuthorizationDate of Change. This should be done for all rows in Change.

This is what I've achieved so far:

UPDATE Change 
SET Change.ap_ITSM_MDP_AuthorizationDate = 
    ( SELECT Journal.CreatedDateTime 
      FROM Journal 
        JOIN Change 
          ON Journal.Parentlink_RecID = Change.RecID 
      WHERE ITSM_ChangeNotes_Subject = 'Status changed to: Authorized' 
        AND Change.RecID = Journal.ParentLink_RecID
    )

Unfortunately, I get the following error message:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

I thought I DID limit the result to one by the WHERE clause (ChangeNotes_Subject = '...'). What did I do wrong?

Upvotes: 2

Views: 14383

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

I think this will work:

UPDATE Change 
SET Change.ap_ITSM_MDP_AuthorizationDate = 
    ( SELECT Journal.CreatedDateTime 
      FROM Journal 
      WHERE ITSM_ChangeNotes_Subject = 'Status changed to: Authorized' 
        AND Change.RecID = Journal.ParentLink_RecID
    ) ;

But using JOIN is better - to avoid any unwanted updates. Here's how:

UPDATE Change 
SET Change.ap_ITSM_MDP_AuthorizationDate = Journal.CreatedDateTime 
FROM Journal 
  JOIN Change 
    ON Change.RecID = Journal.ParentLink_RecID
WHERE ITSM_ChangeNotes_Subject = 'Status changed to: Authorized' ;

Upvotes: 5

Hiren Visavadiya
Hiren Visavadiya

Reputation: 485

UPDATE Change 
SET Change.ap_ITSM_MDP_AuthorizationDate = 
    ( SELECT TOP 1 Journal.CreatedDateTime 
      FROM Journal 
        JOIN Change 
          ON Journal.Parentlink_RecID = Change.RecID 
      WHERE ITSM_ChangeNotes_Subject = 'Status changed to: Authorized' 
        AND Change.RecID = Journal.ParentLink_RecID
    )

Note: Careful to use this query because subquery will return only one row which is expected by you or not, that i have doubt.

Upvotes: 0

Jeff Watkins
Jeff Watkins

Reputation: 6359

As you said, your Journal has five entries for the change's one... use top 1 or max in your subquery to prove that you can make this work. Then you have to consider which of the Journal entries to take the datetime from and alter your subselect to choose only that.

Oh and your "where" clause duplicates your join, discard that part. Also, which table is "ITSM_ChangeNotes_Subject" located, if it's in Change, that's probably the reason you're seeing duplicates in the returned Journal rows.

Run the subquery with an example change ID to see if you really are getting duplicate rows for your search string.

Upvotes: 1

Related Questions