CODEWITHSUNDEEP
sqlsql-serversql-update
Ian Best
Ian Best

Reputation: 530

SQL Update Multiple Rows

UPDATE
  TABLE_1
SET
  COL_1 = (
             SELECT
               SUM(TOT)
             FROM
               TABLE_2
           )
WHERE
  CUST_NO = TABLE_2.CUST_NO

So, this is my SQL so far. Essentially, I am trying to update COL_1 in TABLE_1 to be the sum of the TOT stored in TABLE_2. I am trying to update this on the CUST_NO from both tables.

I know my code doesn't work at all, but I really have no idea how to do this.

So an example of one row from TABLE_1 before I run my SQL would be:

|CUST_NO |COL_1|
|1000    |null |
|1001    |null |

And TABLE_2:

|CUST_NO |TOT  |
|1000    |15   |
|1000    |17   |
|1001    |13   |
|1001    |12   |

And what I ultimately want/need:

TABLE_1:

|CUST_NO |COL_1|
|1000    |32   |
|1001    |25   |

Upvotes: 0

Views: 4556

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

This might be what you want:

UPDATE TABLE_1
    SET COL_1 = (SELECT SUM(TOT)
                 FROM TABLE_2
                 WHERE  table_1.CUST_NO = TABLE_2.CUST_NO    
                )

This assumes you want to change all rows. Otherwise, try this:

UPDATE TABLE_1
    SET COL_1 = (SELECT SUM(TOT)
                 FROM TABLE_2
                 WHERE  table_1.CUST_NO = TABLE_2.CUST_NO    
                )
where exists (select 1 from table_2 WHERE  table_1.CUST_NO = TABLE_2.CUST_NO)

To just change rows where the customer numbers in table1 are in table 2.

Depending on the database, there are probably other ways to express this. However, this syntax should work in most databases.

Upvotes: 3

Related Questions