Dealing with UPDATEing from another table when the JOIN is multiple columns

Question

I'm dealing with UPDATEing from another table when the JOIN is multiple columns

I've tried numerous versions of a SQL statement, but none of them work correctly.

I need to update single fields from one table with data from another table, but the JOIN is on three fields. Sometimes I need to update with SUM() from other fields.

So here's the problem: I'm dealing with this maddening Property Management software that has NO IDENTITY FIELDS!!! (Why? Why would you not use identity fields?!?!?!)

As a result, my linking always involves: dept_no (unique to each property), unit (each unit), lease start date (because there can be multiple leases for each property/unit combination).

I already have a "Lease_unit" table I generated that gave me the MAX(lease_start_date) for each property/unit combination. But then, I often need to update fields in that table with data from other sources, where the link is that dept_no, unit, lease_start_date combination (don't need the MAX() as I've already taken care of that).

So, there's two kinds of problems/examples I cannot figure out that some of you folks can answer in a second (which I've been pounding my head against a wall with for a while). Can someone help me?

Problem/Example #1:

Update lease_unit.field with other_table.other_field
     where lease_unit.dept_no = other_table.dept_no AND lease_unit.unit = other_table.unit AND lease_unit.lease_start_date = other_table.lease_start_date.

Problem/Example #2:

Update lease_unit.field with SUM(other_table.numeric_field)
    where lease_unit.dept_no = other_table.dept_no AND lease_unit.unit = other_table.unit AND lease_unit.lease_start_date = other_table.lease_start_date.

I could really use some guidance here. I really appreciate anyone who could give me a few minutes of your time. Hopefully, some day I'll be able to pass it along to someone else. -- John Kiernan

An update with a JOIN is the same syntax no matter how many columns are used in the JOIN condition. I don't see why it's a "problem" that your tables don't have Identity fields. It's not clear from the pseudo queries that you posted what you were doing wrong in your actual attempts. — Tab Alleman, Aug 26 '19 at 17:06

score 0 · Accepted Answer · answered Aug 26 '19 at 16:38

example 1
You could use update with join

Update lease_unit 
SET  lease_unit.field= other_table.other_field
FROM lease_unit 
INNER JOIN other_table ON lease_unit.dept_no = other_table.dept_no 
    AND lease_unit.unit = other_table.unit 
        AND lease_unit.lease_start_date = other_table.lease_start_date

EXAMPLE 2 use an update with join based on subquery for sum

 Update lease_unit 
 SET  lease_unit.field= t.my_sum
 FROM lease_unit 
 INNER JOIN (
  select  dept_no, unit, lease_start_date, sum(numeric_field) my_sum
  from  other_table
  GROUP BY dept_no, unit, lease_start_date

 ) t ON lease_unit.dept_no = t.dept_no 
      AND lease_unit.unit = t.unit 
          AND lease_unit.lease_start_date = t.lease_start_date

(anyway using an indentity column or 3 column primary key is not a problem)

Dealing with UPDATEing from another table when the JOIN is multiple columns

1 Answers1