SQL update from multiple tables

Question

I have a seemingly basic SQL update to perform on a postgreSQL table that takes values from another table, the table structures are as follows...

sessions_table

session_id (PK)
velocity
time

lengths_table

length_id
session_id
length_dist

I want to update the velocity field with the sum of the length_dist values where session_table.session_id = lengths_table.session_id

I am trying the following statement but get ERROR: null value in column "velocity" violates not-null constraint when I run it (it works fine when I specify a single row)

UPDATE sessions_table 
SET velocity = ROUND((SELECT SUM(lengths_table.length_dist) 
                      FROM lengths_table
                      WHERE lengths_table.session_id = sessions_table.session_id)/time,2)

Can anyone suggest where I am going wrong?

Thanks

David

Just wanted to make sure if it is a typing mistake or not. In your query above, you have SELECT SUM(lengths_table.length_dist) FROM lengths. Shouldn't it be FROM lengths_table. — Aziz Shaikh
– Aziz Shaikh, Commented Sep 13, 2011 at 10:27

StevieG · Accepted Answer · 2011-09-13 10:33:36Z

Either:

Your syntax is wrong (there is no lengths_table). Should be:

UPDATE sessions_table 
SET velocity = ROUND((SELECT SUM(lengths.length_dist) 
                      FROM lengths_table 
                      WHERE lengths.session_id = sessions_table.session_id)/time,2)

or, there are some cases where a sessions_table record has no macthing record in lengths_table. So you need to set velocity to zero in this case to satisfy your non-null constraint:

UPDATE sessions_table 
SET velocity = coalesce(ROUND((SELECT SUM(lengths_table.length_dist) 
                               FROM lengths 
                               WHERE lengths_table.session_id = sessions_table.session_id)/time,2),0)

or, there are some matching records in the lengths_table with null values in length_dist, so you need to replace those with zero for the sum to work:

UPDATE sessions_table 
SET velocity = ROUND((SELECT SUM(coalesce(lengths_table.length_dist,0)) 
                      FROM lengths_table 
                      WHERE lengths_table.session_id = sessions_table.session_id)/time,2)

Collectives™ on Stack Overflow

SQL update from multiple tables

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