Sql-server – TSQL Delete All dates and stores data

• why is the query optimizer not able to use the index for deleting when the subquery version, while it is while using the join version?

Because the optimizer is/was a bit dumb in that regard. Not only for DELETE and UPDATE but for SELECT statements as well, anything like WHERE column IN (SELECT ...) was not fully optimized. The execution plan usually involved running the subquery for every row of the external table (VARIABLE_SUBSTITUTION in this case). If that table is small, everything is fine. If it's large, no hope. In even older versions, a IN subquery with an IN sub-subquery would make even the EXPLAIN to run for ages.

What you can do - if you want to keep this query - is to use the latest versions that have implemented several optimizations and test again. Latest versions meaning: MySQL 5.6 (and 5.7 when it comes out of beta) and MariaDB 5.5 / 10.0

(update) You already use 5.6 which has optimization improvements, and this one is relevant: Optimizing Subqueries with Semi-Join Transformations
I suggest adding an index on (BUILD_KEY) alone. There is a composite one but that's not very useful for this query.

• is there any (ideally standards conform) way to trick it into using the index?

None that I can think of. In my opinion, there is not much worth in trying to use standard SQL. There are so many differences and minor quirks that each DBMS has (UPDATE and DELETE statements are good examples of such differences) that when you try to use something that works everywhere, the result is a very limited subset of SQL.

• is there a portable way to write a delete from join? The application supports PostgreSQL, MySQL, Oracle and Microsoft SQL Server, used via jdbc and Hibernate.

Same answer as the previous question.

• why is the delete from VARIABLE_SUBSTITUTION blocking inserts into BUILDRESULTSUMMARY, which is only used in the subselect?

Not 100% sure but I think it has to do with running the subquery multiple times and what type of locks it is taking on the table.

I am not entirely sure but if the aggregated results are supposed to be per date and the date is production.scheduled_pull_date, then perhaps this:

UPDATE
  pOut
SET
  pOut.lead_ppm = (
    SELECT
      MAX(p1.lead_ppm)
    FROM
      dbo.production AS p1
    WHERE
       (p1.tank = 'B' or p1.tank = 'C')
       AND p1.scheduled_pull_date = pOut.scheduled_pull_date
  ),
  pOut.sulfur_ppm = (
    SELECT
      MAX(p2.sulfur_ppm)
    FROM
      dbo.production AS p2
    WHERE
      (p2.tank = 'B' or p2.tank = 'C')
      AND p2.scheduled_pull_date = pOut.scheduled_pull_date
  )
FROM
  dbo.production AS pOut,
  dbo.assay_temp AS tOut
WHERE
  pOut.tank = 'S'
  AND pOut.cell_num = 1
  AND pOut.scheduled_pull_date = tOut.formatted_date
;

Although I would also rewrite the join using the contemporary syntax:

FROM
  dbo.production AS pOut
  INNER JOIN dbo.assay_temp AS tOut ON pOut.scheduled_pull_date = tOut.formatted_date
WHERE
  pOut.tank = 'S'
  AND pOut.cell_num = 1

The assay_temp doesn't seem needed in the subqueries at all, and instead of matching scheduled_pull_date against formatted_date the subqueries should correlate with the outer query. Since you want aggregations per date, it seemed to me the subqueries should match against pOut.scheduled_pull_date.