Update table from another table for certain fields

Question

I need you help in updating changes a table.

the table i want to update is IP_VISIT_EASTERN_REGION, the field I want to update is VISIT_STAT

so I wrote this query

UPDATE IP_VISIT_EASTERN_REGION SET
IP_VISIT_EASTERN_REGION.VISIT_STAT=TEST_IP_VISIT_EASTERN_REGION.VISIT_STAT
WHERE IP_VISIT_EASTERN_REGION.VISIT_ID = TEST_IP_VISIT_EASTERN_REGION.VISIT_ID

The Issue is I get an error

ORA-00904 INVALID IDENTIFIER

and is this the correct way to do it.

your help will be really appreciated.

thank you

Please do present table structure for clarity. – Charlesliam Jan 29 '14 at 09:07 — Charlesliam, Jan 29 '14 at 09:07

score 0 · Accepted Answer · edited May 23 '17 at 12:11

0

You can't write an UPDATE from a SELECT by using a join. Try this approach instead.

update set from inner join query thowing error

In your case:

UPDATE IP_VISIT_EASTERN_REGION REGION
SET REGION.VISIT_STAT= ( SELECT TEST.VISIT_STAT 
                         FROM TEST_IP_VISIT_EASTERN_REGION TEST
                         WHERE REGION.VISIT_ID = TEST.VISIT_ID )
WHERE EXISTS ( SELECT TEST.VISIT_STAT 
               FROM TEST_IP_VISIT_EASTERN_REGION TEST
               WHERE REGION.VISIT_ID = TEST.VISIT_ID );

Fiddle:

http://sqlfiddle.com/#!4/2178ae/1

edited May 23 '17 at 12:11

Community

1
1

answered Jan 29 '14 at 08:50

Drumbeg

1,914
1
15
22

Thank you but I got this error.. ORA-01427: single-row subquery returns more than one row – FatherofFaris Jan 29 '14 at 08:59
Is `VISIT_ID` not a primary key in `TEST_IP_VISIT_EASTERN_REGION`? I'd assumed it would identify a unique row in your tables? Otherwise, how do you know which record in the `TEST` table to use for your update? – Drumbeg Jan 29 '14 at 09:04
I have a compound key – FatherofFaris Jan 29 '14 at 09:32
So, what is the key? Your `WHERE` should be based on this key. – Drumbeg Jan 29 '14 at 09:35
Include all the fields of composite primary key in WHERE clause so that it will return only one row. Thanks – Avrajit Jan 29 '14 at 09:35

Update table from another table for certain fields

1 Answers1