CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
ssokol91
ssokol91

Reputation: 562

Update statement across multiple fields

I have a table structure (particularly not fond of) where I need to update multiple colums in one row.

Source Table

ID             TotalIntake     TotalOutput     Day
1                 1000            500           0
1                 1500           1000           1
2                 100             200           0

Destination Table (should look like this after update)

ID         TotalIntake_0    TotalIntake_1   TotalOutput_0  TotalOutput_1
1             1000              1500          500             1000
2             100               NULL          200             NULL

I tried to use Case when statement, but for some reason it only updates one of each columns and not all the columns across

  UPDATE e
  Set e.TotalIntake_0 = Case WHEN i.ProcedureDay = 0 Then i.TotalIntake End
      ,e.TotalIntake_1 = Case WHEN i.ProcedureDay = 1 Then i.TotalIntake End  
      ,e.TotalOutput_0 = Case WHEN i.ProcedureDay = 0 Then i.TotalOutput  End
      ,e.TotalOutput_1 = Case WHEN i.ProcedureDay = 1 Then i.TotalOutput End
  FROM DestinationTable e LEFT JOIN SourceTable i ON e.id = i.id

Any ideas greatly appreciated!

Thanks!

Upvotes: 1

Views: 78

Answers (2)

Guillaume CR
Guillaume CR

Reputation: 3016

Wouldn't it be simpler to do this in multiple updates?

UPDATE e
SET e.TotalIntake_0 = i.TotalIntake
FROM DestinationTable e
LEFT JOIN SourceTable i ON e.id = i.id
WHERE i.Day = 0

UPDATE e
SET e.TotalIntake_1 = i.TotalIntake
FROM DestinationTable e
LEFT JOIN SourceTable i ON e.id = i.id
WHERE i.Day = 1

Use transactions if necessary.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Updates on a row are not cumulative. So, only one matching row is used for the update, and you don't know which one. You can do what you want but you need to summarize the rows first and then do the update:

  UPDATE e
      Set TotalIntake_0 = i.TotalIntake_0,
          TotalIntake_1 = TotalIntake_1,   
          TotalOutput_0 = TotalOutput_0,
          TotalOutput_1 = TotalOutput_1
      FROM DestinationTable e LEFT JOIN
           (select i.id,
                   TotalIntake_0 = max(Case WHEN i.ProcedureDay = 0 Then i.TotalIntake End),
                   TotalIntake_1 = max(Case WHEN i.ProcedureDay = 1 Then i.TotalIntake End),
                   TotalOutput_0 = max(Case WHEN i.ProcedureDay = 0 Then i.TotalOutput End),
                   TotalOutput_1 = max(Case WHEN i.ProcedureDay = 1 Then i.TotalOutput End)
            from SourceTable i
            group by i.id
           ) i
           ON e.id = i.id ;

The documentation that describes this is a bit hard to parse:

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic. For example, in the UPDATE statement in the following script, both rows in Table1 meet the qualifications of the FROM clause in the UPDATE statement; but it is undefined which row from Table1 is used to update the row in Table2.

I've highlighted the relevant part.

Upvotes: 2

Related Questions