SQL Update Multiple Rows

Question

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   |

Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it — Taryn
– Taryn, Commented Mar 26, 2013 at 18:11

Gordon Linoff · Accepted Answer · 2013-03-26 18:10:38Z

3

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.

answered Mar 26, 2013 at 18:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Update Multiple Rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related