SQL Statement to Pull a List Using Data from Multiple Tables

Question

Thanks for the response. Here's the problem....the Oildatasetstatusid's (5-11) are mapped to labid=4. Labid=4 has input data for multiple customers, so the update cannot be performed based on oildatasetstatusid. The updates have to be based on samplepointid. That's why 3 tables are being used.

I am among the SQL ignorant and have been asked to perform a task that is way out of my realm of understanding. Our customer wants to change their existing severity levels, e.g. change from Low Warning (severityid=7) to Low (severityid=18). The severity information is entered in the database by a 3rd party oil lab. I want to generate one list using information from three different tables, then change the severity mapping on that list. Have I provided enough information below? Can someone please help????

table: samplepoint
 - samplepointid

table: oildataset
 - oildatasetid
 - oillabid (4=R&G)
 - samplepointid

table: Oildatasetstatus
 - Oildatasetid
 - Oildatasetstatusid (5,6,7,8,9,10,11) needs to be changed to (16,17,18,19,20)
    - 5=16
    - 6=17
    - 7,8,9=18
    - 10=19
    - 11=20

Answer 1

update Oildatasetstatus
set Oildatasetstatusid = 16
where Oildatasetstatusid  = 5

-- and then run a similar command for each of the other status ids

If you want to get a report afterward, you'd run something like this:

select s.samplepointid
, ods.oildatasetid
, ods.oillabid
, odss.oildatsetstatusid
from samplepoint s
, oildataset ods
, oildatasetstatus odss
where ods.samplepointid = s.samplepointid
and odss.oildatasetid = ods.oildatassetid
order by s.samplepointid

Answer 2

I don't know if you'll have do anything else, but if you just want to replace those severities, do this:

UPDATE Oildatasetstatus SET Oildatasetstatusid=16 WHERE Oildatasetstatusid=5;

UPDATE Oildatasetstatus SET Oildatasetstatusid=17 WHERE Oildatasetstatusid=6;

UPDATE Oildatasetstatus SET Oildatasetstatusid=18 WHERE Oildatasetstatusid IN (7, 8, 9);

UPDATE Oildatasetstatus SET Oildatasetstatusid=19 WHERE Oildatasetstatusid=10;

UPDATE Oildatasetstatus SET Oildatasetstatusid=11 WHERE Oildatasetstatusid=20;

If Oildatasetid is the primary key than this should do it.

Be careful and make a backup of the data.

What is the connection between Oildatasetstatus and oildataset?

Answer 3

If I understand your task, here's a statement that might help. You don't say what brand of database software you're using.

Multi-table UPDATE syntax is non-standard SQL and it is supported only by Microsoft SQL Server and MySQL. Here's an example (untested) for MS SQL:

UPDATE Oildatasetstatus
SET Oildatasetstatusid = 
  CASE Oildatasetstatusid
    WHEN 5 THEN 16
    WHEN 6 THEN 17
    WHEN 7 THEN 18
    WHEN 8 THEN 18
    WHEN 9 THEN 18
    WHEN 10 THEN 19
    WHEN 11 THEN 20
    ELSE Oildatasetstatusid
  END CASE
FROM Oildatasetstatus
  JOIN Oildataset USING (Oildatasetid)
WHERE Oildataset.Oillabid = 4;

If you're not confident with SQL, please run this on a copy of your data first, and verify that it works as you expect it to!

Answer 4

To repeat Heavy Bytes: Be Careful!

A handy tip here is to not write any UPDATE or DELETE statements until you know what they're going to affect. This is relatively easy because you can build a SELECT statement with the same where clause.

eg:

UPDATE Oildatasetstatus SET Oildatasetstatusid=18 
WHERE Oildatasetstatusid IN (7, 8, 9)

becomes:

SELECT * FROM Oildatasetstatus 
WHERE Oildatasetstatusid IN (7, 8, 9)

Or you can do it with comments so you can easily turn it back into an update statement when you want to execute it:

-- UPDATE Oildatasetstatus SET Oildatasetstatusid=18 
SELECT * FROM
WHERE Oildatasetstatusid IN (7, 8, 9)

Answer 5

I want to generate one list using information from three different tables

The schema you've posted has no actual information in the samplepoint table, so it'd actually just be information from two tables. Essentially I'm guessing you simply want to join the ‘oillabid’ and ‘samplepointid’ values from ‘oildataset’ into each row of ‘oildatasetstatus’?

SELECT *
FROM oildatasetstatus
JOIN oildataset ON oildataset.oildatasetid=oildatasetstatus.oildatasetid

then change the severity mapping on that list.

I'm guessing you mean you want your query to have different severities, rather than actually UPDATEing your stored ‘oildatasetstatus’ table? If so, you can use the CASE operator:

SELECT
    oildataset.oildatasetid, oildataset.oillabid, oildataset.samplepointid,
    CASE oildatasetstatus.oildatasetstatusid
        WHEN 5 THEN 16
        WHEN 6 THEN 17
        WHEN 7 THEN 18
        WHEN 8 THEN 18
        WHEN 9 THEN 18
        WHEN 10 THEN 19
        WHEN 11 THEN 20
    END AS newstatus
FROM oildatasetstatus
JOIN oildataset ON oildataset.oildatasetid=oildatasetstatus.oildatasetid