0

I'm very new to SQL. I need help in how to update "AH_Field" in database sx_appmts.db, table "Sx_Appmts_table" with a value of "No Show" when I do not find a record in the sx_services_db, table sx_services_table.

This code works fine when it finds a match and puts the value "Done" in the AH_Field, however I wanted to place the value of "No Show" when no match is found.

UPDATE S
SET [AH_Field] = 'Done' 
FROM [SX_Appmts_Db].[dbo].[Sx_Appmts_Table] S 
INNER JOIN [SX_Services_db].[dbo].[Sx_Services_Table] D ON S.[AA_field] = D.[WK_Field] 
WHERE S.[AH_Field] LIKE 'Scheduled' AND
      S.[AA_Field] = D.[WK_Field] AND
      S.[AK_Field] IS NOT NULL AND 
      S.[AQ_Field] IS NOT NULL AND 
      CONVERT(VARCHAR(10), S.[AK_Field], 101) = CONVERT(VARCHAR(10), D.[DV_Field], 101) AND 
      CONVERT(VARCHAR(10), S.[AQ_Field], 101) = CONVERT(VARCHAR(10), D.[AD_Field], 101) AND 
      S.[AD_Field] LIKE D.[AA_Field]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tomtdm
  • 1
  • 1
  • Have you tried if /else? – Iria Jan 12 '20 at 20:47
  • It looks like you are converting two date fields to varchar to compare them, e.g. `convert(varchar(10), S.[AQ_Field], 101) = convert(varchar(10), D.[AD_Field], 101) ` This is pointless and will slow your query down, you can achieve exactly the same thing with the [sargable](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) predicate `CONVERT(DATE, S.[AQ_Field]) = CONVERT(DATE, D.[AD_Field])` – GarethD Jan 12 '20 at 21:09
  • I will try it, but for some reason when I first did a test it would not compare unless I did the CONVERT with VARCHAR. – Tomtdm Jan 12 '20 at 21:46
  • Iria Blanco, can you give me an example of the CASE to use when No record is found and then updating the AH_Field to "No Show" – Tomtdm Jan 12 '20 at 21:48

2 Answers2

0

Use a LEFT JOIN instead of an INNER JOIN because you want the unmatched rows too and a CASE expression or IIF() to update the column:

UPDATE S 
SET [AH_Field] = IIF(D.[WK_Field] IS NULL, 'No Show', 'Done') 
FROM [SX_Appmts_Db].[dbo].[Sx_Appmts_Table] S LEFT JOIN [SX_Services_db].[dbo].[Sx_Services_Table] D 
ON    S. [AA_field] = D.[WK_Field] 
  AND S.[AA_Field] = D.[WK_Field]
  AND S.[AD_Field] LIKE D.[AA_Field]
  AND convert(varchar(10), S.[AK_Field], 101) = convert(varchar(10), D.[DV_Field], 101) 
  AND convert(varchar(10), S.[AQ_Field], 101) = convert(varchar(10), D.[AD_Field], 101) 
WHERE S.[AH_Field] LIKE 'Scheduled' AND S.[AK_Field] IS NOT NULL AND S.[AQ_Field] IS NOT NULL

The conditions that you had in the WHERE clause involving columns from the table [Sx_Services_Table] are moved to the ON clause to preserve the integrity of the LEFT JOIN.
Also I don't see the reason for the operator LIKE in this condition:

S.[AH_Field] LIKE 'Scheduled'

You can use = instead of LIKE.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Begging your pardon, but your query still has an `inner join`, not a `left outer join`, and doesn't have a `case` expression. Did something get lost in posting the code? – HABO Jan 12 '20 at 21:22
  • @HABO thanks, I managed to miss in the code the main point of my answer..... – forpas Jan 12 '20 at 21:23
  • 2
    Ok Agree on = instead of LIKE. – Tomtdm Jan 12 '20 at 21:29
  • Can you give me an example how the IF will look like when no match is found and then putting the "No Show" in the AH_Field" – Tomtdm Jan 12 '20 at 21:30
0

The important point is LEFT JOIN, but I would make a few changes to the query:

UPDATE S
    SET AH_Field = (CASE WHEN ST.WK_Field IS NOT NULL THEN 'Done' ELSE 'No Show' END)
    FROM [SX_Appmts_Db].[dbo].[Sx_Appmts_Table] S LEFT JOIN
         [SX_Services_db].[dbo].[Sx_Services_Table] ST
         ON S.[AA_field] = ST.[WK_Field] AND
            CONVERT(DATE, S.AK_Field) = CONVERT(DATE, ST.DV_Field) AND 
            CONVERT(DATE, S.AQ_Field) = CONVERT(DATE, ST.AD_Field) AND 
           S.AD_Field LIKE ST.AA_Field
WHERE S.[AH_Field] = 'Scheduled';

Notes:

  • There is no need to put columns names in square braces unless they need to be quotes (and having to quote identifiers means you have a bad choice of identifier).
  • D --> ST, an abbreviation for the table name.
  • There is no need to repeat the S.[AA_Field] = D.[WK_Field] comparison.
  • LIKE 'Scheduled' is pretty much equivalent to = 'Scheduled', but the latter is clearer in intent.
  • Date comparisons are best made using date functions, not strings.
  • The comparisons to NULL are superfluous. The equality comparisons already filter out NULL values.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The `AND WHERE` syntax is new to me. I'd guess that the `WHERE` should be removed to avoid changing the `LEFT JOIN` into an `INNER JOIN`. – HABO Jan 12 '20 at 22:22
  • I removed the AND before the WHERE. It executes but no record is updated. Let me be clearer... I just want to update records on the SX_Appmts_Table that are associated with the SX_Services_Table. If a match is found, then place "Done" in the AH_Field, if no match is found then I place "No Show" in the AH_Field. – Tomtdm Jan 12 '20 at 23:16
  • The fields that I use to match the sx_Appmts_Table record are Chart#(S.AA_Field to D.WK_Field), Date of Service (S.AK_Field to D.DV_Field), Date of Birth (S.AQ_Field to D.AD_Field), Type of Test(S.AD_Field to D.AA_Field) and the S.AH_Field must have "Scheduled".. If I find a match then I know the patient came in and I update the S.AH_Field to "Done" , if no match then I know the patient did not have a service done (no record found in SX_Services_Table), I then update the S.AH_Field to "Done". – Tomtdm Jan 12 '20 at 23:16
  • Here is my last update after recommendations. The update should be based on whether a match is found... – Tomtdm Jan 12 '20 at 23:19
  • UPDATE S SET AH_Field = (CASE WHEN WK_Field IS NOT NULL THEN 'Done' ELSE 'No Show' END) FROM [SX_Appmts_Db].[dbo].[Sx_Appmts_Table] S LEFT JOIN [SX_Services_db].[dbo].[Sx_Services_Table] ST ON S.[AA_field] = ST.[WK_Field] WHERE S.[AH_Field] = 'Scheduled' AND convert(DATE, S.AK_Field) = convert(DATE, ST.DV_Field) AND convert(DATE, S.AQ_Field) = convert(DATE, ST.AD_Field) AND S.AD_Field = ST.AA_Field – Tomtdm Jan 12 '20 at 23:22
  • @Tomtdm . . . The comparisons *between* the tables all need to be in the `ON` clause. The filtering on the first tables needs to be in the `WHERE`. I updated the answer. – Gordon Linoff Jan 13 '20 at 01:28
  • Thank you so much Gordon for your help....I really appreciate your help and effort in getting the correct code to work. I tried it and all seems to be working as intended. – Tomtdm Jan 13 '20 at 15:11
  • hi Gordon Im still stuck in the code. your suggestion works fine when a record is found on services by setting the value of "Done". however, when there is no record matching it does not return the value of "No Show". I dont know why... – Tomtdm Sep 30 '21 at 00:41