StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>How about breaking it down and testing whether you are getting the right results and incrementally putting them together? </p> <p>The WHERE condition for the initial UPDATE (edited to have INNER JOINs instead of SELECT INs) and also selected <strong>oildatasetid not oildatasetstatusid</strong> :</p> <pre><code>SELECT oildatasetid FROM oildatasetstatus INNER JOIN oildataset ON oildataset.oildatasetid = oildatasetstatus.oildatasetid INNER JOIN samplepoint ON oildatasetstatus.samplepoinid = samplepoint.samplepointid INNER JOIN customersite ON samplepoint.customersiteid = customersite.customersiteid WHERE customerid = 2; </code></pre> <p>This should return the same list of oildatasetid as your query but run faster.</p> <p>Now the UPDATE statement:</p> <pre><code>UPDATE oildatasetstatus SET oildatasetstatusid = CASE WHEN oildatasetstatusid = 5 THEN 16 WHEN oildatasetstatusid = 6 THEN 17 WHEN oildatasetstatusid = 7 THEN 18 WHEN oildatasetstatusid = 8 THEN 18 WHEN oildatasetstatusid = 9 THEN 18 WHEN oildatasetstatusid = 10 THEN 19 WHEN oildatasetstatusid = 11 THEN 20 END WHERE oildatasetid IN ( SELECT oildatasetid FROM oildatasetstatus INNER JOIN oildataset ON oildataset.oildatasetid = oildatasetstatus.oildatasetid INNER JOIN samplepoint ON oildatasetstatus.samplepoinid = samplepoint.samplepointid INNER JOIN customersite ON samplepoint.customersiteid = customersite.customersiteid WHERE customerid = 2 ); </code></pre> <p>I avoid BETWEEN unless absolutely necessary:</p> <pre><code>UPDATE oildataset SET oillabid = 9 WHERE oildatasetstatusid >= 16 AND oildatasetstatusid <= 20; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload