Update Table By Pulling Information from Multiple Tables

Question

I tried the statement as it is below and it didn't work. Query Analyzer returned a "(0) rows affected". The good news...it didn't blow up....the bad news....I don't know what is wrong with the statement.

I think I am using SQL Server 2000. I haven't tried the statement yet on my demo database. The second statement is to update the old oillab (oillabid=4) to the new oillab (oillabid=9). The oillabid is mapped to the oillabstatusid's that are being changed in the oillabstatus table.

I want to update the oildatasetstatusid in the oildatasetstatus table by customerid. In order to get the customerid, I need to get the oildatasetid (mapped to the oildatasetstatus table) and the samplepointid from the oildataset table. The samplepointid is mapped to the samplepoint table which also contains the customersiteid. The customersiteid is mapped to the customersite table which contains the customerid. After that update is complete, I need to update the oillabid in the oildataset table. I am guessing this has to be done using two statements (shown below). Do these statements look correct or are there any glaring mistakes? Is there anyway to do both of these updates in one statement? Thanks.

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 oildatasetstatusid from oildatasetstatus 
 inner join oildataset on oildataset.oildatasetid=oildatasetstatus.oildatasetid
where samplepoinid in
 (
 select samplepointid from samplepoint where customersiteid in
  (
  select customersiteid from customersite where customerid=2
  )
 )
)

update oildataset set oillabid=9 where oildatasetstatusid between 16 and 20

Answer 1

The 3 selects in the where clause of the update could be the problem. What results are you getting? What is the second update doing? I don't think you can do the update for both tables in one update, since they are different tables.

Answer 2

Is there anyway to do both of these updates in one statement?

do you want to UPDATE 2 different tables in 1 statement???

Answer 3

How about breaking it down and testing whether you are getting the right results and incrementally putting them together?

The WHERE condition for the initial UPDATE (edited to have INNER JOINs instead of SELECT INs) and also selected oildatasetid not oildatasetstatusid :

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;

This should return the same list of oildatasetid as your query but run faster.

Now the UPDATE statement:

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
);

I avoid BETWEEN unless absolutely necessary:

UPDATE oildataset SET oillabid = 9 WHERE oildatasetstatusid >= 16 AND oildatasetstatusid <= 20;