How to write a loop as a SQL query efficiently

Question

How do I code the following algorithm as a SQL query (a and b and c are are tables in my database)

for all a:
case c.column=0
set a.columnname+=b.column1*b.column2
case c.column=1
...

earlier i solved a similar problem by:                          
UPDATE a 
set
a.column= (select SUM(column1) from b where a.column2=b.column2)

but since in is summing a product of two columns i don't think i can do the same. Also the real problem lies with the one to mny relationship a has with c.

relationships:

a one to many b.

b one to one c

lets say A is a table of company data, B is a table of employee data and C tells us if a employee is male or female(just an example not really my problem). Now i need to calculate the total salary given to each employee for each company and store it in a field in the company table.lets say i calculate this differently based on employees gender. Again now there a hundreds of companies and each company has thousands of employees.

you could look into cursors, but used wrong, they suck. why do you need a loop in sql? I have thought the same many times in the past, and have usually been shown a way to do it with a one line select or update statement. — mmeasor, Mar 12 '14 at 20:37
How are your tables related to each other? What is the rest of the logic? Is there some uniformity? — PM 77-1, Mar 12 '14 at 20:41
Are your tables related to each other? Can you state what you're trying to do in plain English instead of psuedo-code? — Andrew, Mar 12 '14 at 20:42
@user2197558 Conversion from procedural languages to set-based languages is almost never that straight-forward. — D Stanley, Mar 12 '14 at 20:44

score 1 · Answer 1 · edited May 23 '17 at 10:29

1

you probably want to break it down into a series of queries with the "cases" from the case statement as different WHERE clauses :

UPDATE <thing>
SET .......
FROM a,b c
INNER JOIN .......
WHERE c.column=0;


UPDATE <thing>
SET .......
FROM a,b c
INNER JOIN .......
WHERE c.column=1;

See these for examples of the type of syntax :

SQL update query syntax with inner join

SQL update query using joins

edited May 23 '17 at 10:29

Community

1
1

answered Mar 12 '14 at 20:40

Angst

304
1
9
14

since there is a one to many relationship between a and b a join would populate too many rows. – user2197558 Mar 12 '14 at 20:46
hm, you might also then want to create some temporary table with the values to update, to avoid the one-to-many problem. I think Rinze Smits in the other answer has a solution where this is all in the one query. – Angst Mar 12 '14 at 22:28

D Stanley · Answer 2 · 2014-03-12T21:50:49.043

1

If you're updating columnname in each case then you don't need a loop:

UPDATE a
SET columnname = 
   CASE WHEN c.column=0
      THEN columnname + b.column1*b.column2
   CASE WHEN c.column=1
      THEN ...

otherwise it would be cleaner as multiple UPDATEs with different WHERE clauses

Based on your update and comment something like this might work:

UPDATE a 
set a.column = 
(SELECT 
 SUM(
     CASE WHEN c.EmployeeGender = 0
            THEN b.column1*b.column2
          WHEN c.EmployeeGender = 1
            THEN ... 
          ELSE 
     END
     )
 FROM b 
 INNER JOIN c
    ON b.EmployeeID = c.EmployeeID
 WHERE a.column2=b.column2)

edited Mar 12 '14 at 21:50

answered Mar 12 '14 at 20:43

D Stanley

149,601
11
178
240

i am actually trying to get around using a join due to the one to many relationship between a and b. it populates too many rows. – user2197558 Mar 12 '14 at 20:50
How else do you relate `a` to `b` if you can't join them? – D Stanley Mar 12 '14 at 20:51
i updated to show how i did it for an earlier example – user2197558 Mar 12 '14 at 21:29

Rinze Smits · Accepted Answer · 2014-03-12T22:40:52.050

To reduce a set you use GROUP BY and an aggregate function, like SUM. For each record in A you want to get the sum of some function on all related records in B and C.

First we'll make a SELECT statement to be sure we get the right data. This will look something like:

SELECT 
  a.id,
  SUM(CASE WHEN c.[column]=0 THEN b.column1 * b.column2 ELSE 0 END) +
    SUM(CASE WHEN c.[column]=1 THEN b.column1 + b.column2 ELSE 0 END) AS new_value
FROM a
INNER JOIN b ON a.id = b.a_id
INNER JOIN c ON b.id = c.b_id
GROUP BY a.id --and all other columns from A that you select/update, making sure it contains at least the columns required to select a unique record from a.

To convert this to an update statement you can do a 1:1 join to the regular table A:

UPDATE updated_a
  SET columnname = new_value
  FROM a AS updated_a INNER JOIN
   (SELECT 
  a.id,
  SUM(CASE WHEN c.[column]=0 THEN b.column1 * b.column2 ELSE 0 END) +
    SUM(CASE WHEN c.[column]=1 THEN b.column1 + b.column2 ELSE 0 END) AS new_value
FROM a
INNER JOIN b ON a.id = b.a_id
INNER JOIN c ON b.id = c.b_id
GROUP BY a.id) AS calculation ON updated_a.id = calculation.id;

http://sqlfiddle.com/#!6/2dffa/14

How to write a loop as a SQL query efficiently

3 Answers3