Punjabi Killaz
New Member
I have a database named SCORPADData and it's got two tables, Parts and Failure_Rates. I'm trying to take the column Reparable in the Parts table and set it as a boolean value based on the value in the condemnation rate table, which is somewhere between 0 and 100.0. A condemnation rate of 100.0 means the part cannot be repaired. I'm doing this because I've been asked to take one database and make it readable to software that originally read from another in a one-time update. I've tried to do this in two different ways: using Case, and using Where with a set value of false. The statements are listed below.\[code\]UPDATE SCORPADData.Parts SET Reparable = CASE WHEN SCORPADData.Failure_Rates.Part_ID = SCORPADData.Parts.ID AND SCORPADData.Failure_Rates.Condemnation_Rate = 100.0 THEN 'TRUE' ELSE 'FALSE' END FROM SCORPADData.Parts , SCORPADData.Failure_Rates\[/code\]The error I'm getting is "Column SCORPADDATA.FAILURE_RATES.CONDEMNATION_RATE not found". \[code\]UPDATE SCORPADDATA.PARTS SET REPARABLE = TRUEUPDATE SCORPADDATA.PARTS SET REPARABLE = FALSE FROM SCORPADDATA.FAILURE_RATES WHERE SCORPADDATA.PARTS.ID = SCORPADDATA.FAILURE_RATES.PART_ID AND SCORPADDATA.FAILURE_RATES.CONDEMNATION_RATE = 100.0\[/code\]The error I'm getting here is a syntax error; it doesn't like my placement of FROM. Placing it after the WHERE also doesn't work because it doesn't find the column. In both cases it's my thinking that the FROM isn't working as I was hoping because the FAILURE_RATES table isn't used in the UPDATE SET statement. I'm working with an H2 database in a java application. I'm new to SQL, so my apologies if this is something I should know, but searching Google (or SO) for words like WHERE and FROM doesn't get anyone anywhere.