SQL update table based on JOIN-query-CodePudding

I have the following SQL-query

SELECT holes.hole_x, holes.hole_y, holes.hole_z
FROM holes
LEFT OUTER JOIN shots
    ON holes.tournament = shots.tournament
    AND holes.course = shots.course
    AND holes.year = shots.year
    AND holes.round = shots.round
    AND holes.hole = shots.hole
WHERE shots.end = 'hole'
    AND shots.x = '0.0'
    AND shots.y ='0.0'
    AND shots.z = '0.0'

How do I change this query so that the zero values for x, y, z in shots is updated with the returned values from holes?

Thanks

CodePudding user response：

Just remove SELECT and add UPDATE :

UPDATE s
    SET s.x = h.hole_x, s.y = h.hole_y, s.z = h.hole_z, 
FROM holes h
LEFT OUTER JOIN shots s
    ON h.tournament = s.tournament
    AND h.course = s.course
    AND h.year = s.year
    AND h.round = s.round
    AND h.hole = s.hole
WHERE shots.end = 'hole'
    AND s.x = '0.0'
    AND s.y ='0.0'
    AND s.z = '0.0'

CodePudding user response：

PostgreSQL:

update shots s 
set
  x = h.x, 
  y = h.y, 
  z = h.z 
from holes h 
where 
    s.hole = h.hole and 
    s."year" = h."year" and 
    s.x = 0 and
    s.y = 0 and
    s.z = 0

SQL Server:

UPDATE SHOTS  
SET
  X = H.X, 
  Y = H.Y, 
  Z = H.Z 
FROM SHOTS S
INNER JOIN HOLES H on 
    S.HOLE = H.HOLE 
    AND S.[YEAR] = H.[YEAR]
WHERE 
    S.X = 0 AND 
    S.Y = 0 AND
    S.Z = 0