How to handle updation of atable via another table?

Question

0.00/5 (No votes)

See more:

HELLO,
I m having 2 tables
[1] asset (master table with PK AID)
[2] asset_option(sub table with PK AO_ID)
[3] I wanna set bit = true of asset table whenever record deleted (default value of bit column is false)
[4] I cannot assign AID in where clause bcoz their are multiple entries of same asset with same id at front end so m using AO_ID for deleting only single record.

<br />
Private Sub BtnDelete1_Click()<br />
SQL = "UPDATE (SELECT A.*,S.AO_ID From Asset A, ASSET_OPTION S"<br />
SQL = SQL + " Where A.AID = S.AID AND AO_ID= " & Text1.Text & ")"<br />
SQL = SQL + " SET BIT='TRUE' "<br />
RS.Open SQL, CON, 1, 2<br />
End Sub<br />

I did this at back-end also,it shows error
SET BIT='TRUE'
*
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Posted 19-Mar-13 2:13am

surkhi

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rodrigo Arruda · Accepted Answer · 2013-03-19T02:53:00

Solution 1

I Tried using another way:

SQL

UPDATE (SELECT A.BIT,S.AO_ID FROM Asset A, ASSET_OPTION S WHERE A.AID = S.AID) D
SET D.BIT='TRUE' WHERE D.AO_ID = (value)

If this doesn't work, please post ur CREATE TABLE script to help in the tests.

Posted 19-Mar-13 2:53am

Rodrigo Arruda

Updated 19-Mar-13 2:54am

v2

Comments

surkhi 20-Mar-13 1:41am

Sir,this command also gives same kinda error........


SQL> desc asset
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 AID                                       NOT NULL NUMBER(38)
 ASSET_NAME                                         VARCHAR2(50)
 LOC_ID                                             NUMBER(38)
 UNIT                                               VARCHAR2(25)
 SUP_ID                                             NUMBER(38)
 BIT                                       NOT NULL VARCHAR2(5)

SQL> desc asset_option
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 IDLE_SINCE_DATE                                    DATE
 REASON_IDLE                                        VARCHAR2(30)
 UTILIZED_PERIOD                                    DATE
 FREE_ON_DATE                                       DATE
 CURRENT_STATUS                                     VARCHAR2(50)
 AID                                                NUMBER(38)
 LOC_ID                                             NUMBER(38)
 ESTIMATED_REPAIR_COST                              FLOAT(126)
 EXPECTED_VAL                                       FLOAT(126)
 OFFER_RECEIVED                                     FLOAT(126)
 AO_ID                                     NOT NULL NUMBER(38)
 P_DATE                                             DATE
 P_COST                                             FLOAT(126)