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
– mmeasor, Commented Mar 12, 2014 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
– PM 77-1, Commented Mar 12, 2014 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
– Andrew, Commented Mar 12, 2014 at 20:42
@user2197558 Conversion from procedural languages to set-based languages is almost never that straight-forward. — D Stanley
– D Stanley, Commented Mar 12, 2014 at 20:44

Community · Accepted Answer · 2017-05-23 10:29:54Z

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, 2017 at 10:29

CommunityBot

11 silver badge

answered Mar 12, 2014 at 20:40

Angst

3041 gold badge10 silver badges15 bronze badges

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

2 Comments

user2197558 Over a year ago

since there is a one to many relationship between a and b a join would populate too many rows.

Angst Over a year ago

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.

D Stanley · Accepted Answer · 2014-03-12 21:50:49Z

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, 2014 at 21:50

answered Mar 12, 2014 at 20:43

D Stanley

153k12 gold badges189 silver badges257 bronze badges

3 Comments

user2197558 Over a year ago

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.

D Stanley Over a year ago

How else do you relate a to b if you can't join them?

user2197558 Over a year ago

i updated to show how i did it for an earlier example

Rinze Smits · Accepted Answer · 2014-03-12 22:40:52Z

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

Collectives™ on Stack Overflow

How to write a loop as a SQL query efficiently

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related