Databases
StackOverflow2013
Postsdbo
POUpdate Table By Pulling Information from Multiple Tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate Table By Pulling Information from Multiple Tables
    primarykey
    data
    text
    <p>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. </p> <p>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.</p> <p>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.</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 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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USErin Karschnik
    1. USErin Karschnik
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI suggest cleaning up the question, formatting your sql, and providing some sample data, just to make the question easier to understand.
      singulars
      1. POUpdate Table By Pulling Information from Multiple Tables
      1. USachinda99
    2. COProviding the SQL server you're using helps, too (MySql, Postgres, MsSql, etc.). Many things can be tailored to your environment, resulting in very short and/or highly optimized queries
      singulars
      1. POUpdate Table By Pulling Information from Multiple Tables
      1. USsoulmerge
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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