SQL query to update an empty field to match one from another table

Question

I'm trying to update a date field in an HFSQL database. In the Patient table there is a Patient.PatInserted field where some of the records are blank. The patient IDs are Patient.PatID. I am trying to fill the Patient.PatInserted fields to match the oldest record from another table. The table is called Test and the records from here are tied to the Patient table using Test.TesPatIDFK. I want to insert the oldest Test.TesInserted record for each patient into their Patient.PatInserted one.

I'm new to SQL and I can't get the select query to show more than just the oldest Patient.PatInserted record. I'm trying to get the oldest record for each individual TesPatIDFK. I'm trying to just do the SELECT portion for now.

This is my latest code for the SELECT query:

SELECT
  DISTINCT Patient.PatID,
  Patient.PatInserted,
  Patient.PatSalutation,
  Test.TesPatIDFK,
  Test.TesInserted 
FROM 
  Patient JOIN Test ON Patient.PatID = Test.TesPatIDFK
WHERE
  Patient.PatID = Test.TesPatIDFK
AND
  Test.TesInserted = (SELECT MIN(Test.TesInserted) FROM Test)
AND
  Patient.PatInserted LIKE ''

Here is a basic view of the table structure. I believe PatInserted and TesInserted are timestamps, just with slightly different formats:

      Patient
___________________
PatID | PatInserted
___________________
1,626 | 
___________________
   2  | 16/02/2021

                  Test
_______________________________________
TesID | TesPatIDFK |    TesInserted
_______________________________________
 1303 |    1,626   |25/10/1201 15:04:34
_______________________________________
  8   |     2      |16/02/2021 14:05:51

This is the result from my query:

PatID | PatInserted | TesPatIDFK |    TesInserted
______________________________________________________
1,626 |             |    1,626   | 25/10/1201 00:00:00

Answer 1

1. You want to update the column PatInserted in the table Patient, so update patient set patinserted = ... where ....
2. You want to update empty PatInserted. This should be a timestamp, so I'd expect where patinserted is null. You, however, use PatInserted LIKE '', which assumes that PatInserted is a string and that string is not null, but the empty string. And LIKE doesn't seem to make much sense, anway. LIKE does pattern matching. If you are looking for equality, use =. If the column is a string, you should change it to TIMESTAMP and use where patinserted is null.
3. In order to find the minimum TesInserted for a PatID, use a correlated subquery (correlated = related to the updating row by refering to its ID).

The complete statement:

update patient 
set patinserted = 
(
  select min(test.tesinserted) 
  from test
  where test.tespatidfk = patient.patid
)
where patinserted is null;