Difference between revisions of "Technical Notes"

Revision as of 13:45, 18 January 2008

• Query to find trap_id's in the trap_routes table which are unmatched in the trap table:

SELECT 
  trap_routes.trap_id, trap.trap_id
FROM 
  trap_routes LEFT JOIN trap ON trap_routes.trap_id = trap.trap_id
WHERE 
  trap.trap_ID IS NULL 

• Query to update trap table using values from trap_routes table:

UPDATE 
  trap, trap_routes 
SET 
  trap.trap_route=trap_routes.trap_route, 
  trap.trap_route_sequence=trap_routes.trap_route_sequence 
WHERE 
  trap.trap_id=trap_routes.trap_id

• Query to add extra column with observation_date format as m/d/Y:

SELECT *,DATE_FORMAT(observation_date,'%m/%d/%Y') AS US_observation_date FROM crb_observations;