CODEWITHSUNDEEP
sqlpostgresql
Spatial Digger
Spatial Digger

Reputation: 1993

SQL UPDATE based on SELECT from another table

I have two tables: samples_specific_storage sss and samples s, both have a primary key of 4 columns area_easting, area_northing, context_number, sample_number

Table storage has box numbers, e.g. box F52, I first select all boxes = F52

SELECT sss.area_easting, sss.area_northing, sss.context_number, 
sss.sample_number
FROM samples.samples_specific_storage sss  
WHERE sss.container_name = 'F19'

I want to use this selection to update the records in the samples table which have the corresponding area_easting, area_northing, context_number, sample number. I'm trying to understand nesting queries because I think that is what I need: UPDATE SET FROM WHERE (SELECT...) I know the following is wrong, but something along the lines of:

UPDATE samples.samples_specific_storage
SET current_location = 'testing'
WHERE (
SELECT s.area_easting,s.area_northing,s.context_number,s.sample_number FROM samples.samples s WHERE current_location = 'F19')

Dummy data:

sss

area_easting,area_northing,context_number,sample_numbers,box_number
99,45,1,1,F52
99,44,2,1,F52
99,44,2,2,F52
99,44,5,1,F52
...

samples s area_easting,area_northing,context_number,sample_numbers,shelf_number
99,45,1,1,shelf1
99,44,2,1,shelf1
99,44,2,2,shelf1
99,44,5,1,shelf1
...

So I want to UPDATE shelf1 to shelf2 where the box_number=F52 [I know the structure of the tables is awful - I inherited it, it will be fixed]

Upvotes: 1

Views: 80

Answers (1)

nozzleman
nozzleman

Reputation: 9649

I hope i got your question right, but sth. like this should get you started:

UPDATE samples t1
SET shelf_number='shelf2'
FROM (SELECT sss.area_easting, sss.area_northing, sss.context_number, sss.sample_number
      FROM samples.samples_specific_storage sss  
      WHERE sss.container_name = 'F19') t2
WHERE t1.area_easting   = t2.area_easting   
  AND t1.area_northing  = t2.area_northing 
  AND t1.context_number = t2.context_number
  AND t1.sample number  = t2.sample number

I uses Postges's UPDATE...FROM-Syntax, which isn't Standard-SQL but very handy in situations like that.

Upvotes: 1

Related Questions