Hi I have 2 tables A,B.I want t

[cyno]

Hi

I have 2 tables A,B.I want to update table A based on status
in table B.Iam trying to run this update stmt,but iam getting error.The same update stmt works in MSSQL.Iam new to Oracle.Any help is appreciated.

Update table A a,B b set a.status='XYZ' where a.ID=b.ID
and a.name=b.name and b.type='abc';

 

[carp]

I think this should work for you:

Update table A a
set status='XYZ'
WHERE EXISTS (SELECT 'x'
FROM B b WHERE a.ID=b.ID
and a.name=b.name and b.type='abc');

3 Dec 03/1341 MST

 

[cyno]

Hi carp

Thank u very much its working fine.One more thing i would like to ask is how to view the indexes on a table like we see sp_helpindex table name in MSSQL.

Regards
Cyno

 

[carp]

SELECT index_name
FROM all_indexes
WHERE table_name = 'A';

will give you information about the indexes.


SELECT index_name, column_name, column_position
FROM all_ind_columns
WHERE table_name = 'A'
ORDER BY column_position;

will tell you which column(s) the indexes are build upon.