"Update" SQL help

[fatherofthree]

The loan operating system we use has an oracle background. We are able to create business rules to control how things work in the system.
I am trying to create a rule which will update 5 fields in a table based on the value of a certain field in another table. The primary key of both tables is the AP_NUM. Unfortunately, the code updates the 5 fields on every account no matter what the value is in a certain field. Below is the code:

"SHIP is the table the 5 fields are in"
"T is the table where the criteria field exists"
"CONDO is the criteria for T.PROPERTY_TYPE"
"The statement T.AP_NUM =:FORMNAME.cmbApNum is a required statement for the loan operating system which basically means the ApNum on the active screen equals the value in T.AP_NUM"

UPDATE SHIP SET SHIP.DETACHED_HOUSE = 0, SHIP.ATTACHED_HOUSE = 0, SHIP.PUD = 0, SHIP.CO_OP = 0,
SHIP.CONDO = 1  WHERE EXISTS (SELECT T.PROPERTY_TYPE FROM T WHERE T.PROPERTY_TYPE = 'CONDO' AND T.AP_NUM =:FORMNAME.cmbApNum)

Result:

SHIP.DETACHED_HOUSE is set to 0 for every account no matter what the value of T.PROPERTY_TYPE.

I guess I am missing something. HELP

 

[neecie]

If you are trying to update the SHIP table, shouldn't it be

 WHERE SHIP.AP_NUM = T.AP_NUM  (or formfield)

You can try either of the below options

UPDATE SHIP SET SHIP.DETACHED_HOUSE = 0, SHIP.ATTACHED_HOUSE = 0, SHIP.PUD = 0, SHIP.CO_OP = 0,
SHIP.CONDO = 1  WHERE EXISTS (SELECT T.PROPERTY_TYPE FROM T WHERE T.PROPERTY_TYPE = 'CONDO' AND T.AP_NUM =:FORMNAME.cmbApNum)

 AND SHIP.AP_NUM = T.AP_NUM

OR

Declare @Type varchar (10)
SELECT @Type = T.PROPERTY_TYPE FROM T WHERE T.AP_NUM =:FORMNAME.cmbApNum

IF @Type = 'CONDO'
BEGIN
UPDATE SHIP SET SHIP.DETACHED_HOUSE = 0, SHIP.ATTACHED_HOUSE = 0, SHIP.PUD = 0, SHIP.CO_OP = 0,
SHIP.CONDO = 1  WHERE SHIP.AP_NUM = :FORMNAME.cmbApNum

 

[fatherofthree]

I am not able to use your second suggestion and when I use the first one I get the following database error:

Error number: -904, position: 0-ORA-00904:
Invalid Column Name

The field AP_NUM does exist in the SHIP table.

I brought your edit into the parentheses and I did not get the error but the same updates happened. SHIP.DETACHED_HOUSE was updated to 0 for every account.

Thanks

Chad

 

[SQLSister]

it might be best to put this in an ORacle forum, this forum is for Microsoft SQL Server and our syntax might not work for you. SQL Server would allow me to use a join inthe update statement, but I don't know if Oracle allows that.

There seems to be something missing from your logic and that is what field would join the two data sets together. How can it tell which records to update? it seems to me you are selecting the wrong thing in your subselect statement. It isn't property type that links the two tables is it? That is what the query you gave indicates to me, but that doesn't make logical sense to me.