I am updating the comments column in the gscc table but my update query isn't working how I need it to. There are many more rows in the tables but the issue I am having is focused around when the job, suffix, and seq are the same. how can I update the comments field in gscc with the text from the job_dtl_notes table? The query I tried only searched the first matching row in job_dtl_notes (query below), I know it's because of the where clause. Is there a way I can tweak it to update for my expected results? *Note, the "date" field in job_dtl_notes is a char field and date in gscc is a datetime
query:
update gab_source_cause_codes
set comments = job_dtl_notes.text from job_dtl_notes where job_dtl_notes.job = gab_source_cause_codes.job and job_dtl_notes.suffix = gab_source_cause_codes.suffix and job_dtl_notes.job_seq = gab_source_cause_codes.seq and job_dtl_notes.date > '221231'
Job_dtl_notes Table:
| Job | Suffix | Job_Seq | Date | Text |
|---|---|---|---|---|
| 306012 | 000 | 006900 | 230324 | wrong motor |
| 306012 | 000 | 006900 | 230324 | dirty |
| 306012 | 000 | 006900 | 230325 | outboard idler spacer 7mm wrong |
GSCC Table after my update:
| Job | Suffix | Seq | Date | Comments |
|---|---|---|---|---|
| 305717 | 000 | 002800 | 2023-03-25 08:41:03.000 AM | Missing a pair of parts 27 and28 for the leg |
| 306012 | 000 | 006900 | 2023-03-25 06:47:48.000 AM | wrong motor |
| 306012 | 000 | 006900 | 2023-03-24 10:27:22.000 AM | wrong motor |
| 306012 | 000 | 006900 | 2023-03-24 10:06:08.000 AM | wrong motor |
Expected:
| Job | Suffix | Seq | Date | Comments |
|---|---|---|---|---|
| 305717 | 000 | 002800 | 2023-03-25 08:41:03.000 AM | Missing a pair of parts 27 and28 for the leg |
| 306012 | 000 | 006900 | 2023-03-25 06:47:48.000 AM | outboard idler spacer 7mm wrong |
| 306012 | 000 | 006900 | 2023-03-24 10:27:22.000 AM | wrong motor |
| 306012 | 000 | 006900 | 2023-03-24 10:06:08.000 AM | dirty |