Column Update statement for oracle 10g

How many rows returned by following query ?

select STG_AGENCY.AGY1_AGENCY_ID from STG_AGENCY
where STG_AGENCY.AGY1_NAME = STG_CONTACT_FULL.COMPANY)
AND ROWNUM <= 1

The following query returns an error below


STG_CONTACT_FULL"."COMPANY": invalid identifier

i think this will give sql error because syntax of whatever update you have given is wrong
at the end:

update STG_CONTACT_FULL  
set STG_CONTACT_FULL.POLAR_ID = (select STG_AGENCY.AGY1_AGENCY_ID from STG_AGENCY
where STG_AGENCY.AGY1_NAME = STG_CONTACT_FULL.COMPANY)
AND ROWNUM <= 1)

I assume it will be something like :

update STG_CONTACT_FULL  
set STG_CONTACT_FULL.POLAR_ID = (select STG_AGENCY.AGY1_AGENCY_ID from STG_AGENCY
where STG_AGENCY.AGY1_NAME = STG_CONTACT_FULL.COMPANY AND ROWNUM <= 1);

note: your update does not have a where clause so all records in the table
STG_CONTACT_FULL will get updated. meaning if there is a match, then data will be updated
otherwise null will be updated and this might lead to overwrite of existing values
present in the column POLAR_ID

if upper case is creating a problem, then use

update STG_CONTACT_FULL  
set STG_CONTACT_FULL.POLAR_ID = (select STG_AGENCY.AGY1_AGENCY_ID from STG_AGENCY
where STG_AGENCY.AGY1_NAME = UPPER(STG_CONTACT_FULL.COMPANY) AND ROWNUM <= 1);

Even if all the letters in AGY1_NAME are not in caps, but you want to consider them as CAPS
then use

update STG_CONTACT_FULL  
set STG_CONTACT_FULL.POLAR_ID = (select STG_AGENCY.AGY1_AGENCY_ID from STG_AGENCY
where UPPER(STG_AGENCY.AGY1_NAME) = UPPER(STG_CONTACT_FULL.COMPANY) AND ROWNUM <= 1);


Thanks

It would be better if you can explain why and hwo you want to use ROWNUM <= 1?

>>I think the problem is STG_AGENCY.AGY1_AGENCY_ID  happens to be all caps where as STG_CONTACT_FULL.COMPANY happens to be not caps. Is there the problem or is there a way to specify the cases?

Capitalization is not important - that's not your problem. Explain what you want to accomplish.

>>It would be better if you can explain why and hwo you want to use ROWNUM <= 1?

I would also like to know this. ROWNUM <=1 will return an unpredictable row, why would you want to update such a row?

Hi rrisal,
I think the query you have pasted has mis-matching paranthesis.
update STG_CONTACT_FULL  
set STG_CONTACT_FULL.POLAR_ID = (select STG_AGENCY.AGY1_AGENCY_ID from STG_AGENCY
where STG_AGENCY.AGY1_NAME = STG_CONTACT_FULL.COMPANY) --Extra paranthesis
 AND ROWNUM <= 1)

Make it like:
update STG_CONTACT_FULL  
set STG_CONTACT_FULL.POLAR_ID = (select STG_AGENCY.AGY1_AGENCY_ID from STG_AGENCY
where STG_AGENCY.AGY1_NAME = STG_CONTACT_FULL.COMPANY
AND ROWNUM = 1)

This query will work fine assuming there is no case problem with AGY1_name and COMPANY. If there is use any of these: UPPER/LOWER.