Need help with an oracle UPDATE [modified]

[Kalyan_A]

I have two tables with the following structure. Table 1
Name Field1

n1 a
n2 b
n2 c
n3 d

Table 2
ID Name Field1

1 n1 NULL
2 n2 NULL
3 n3 NULL

Usually, Table1.Name and Table1.Field1 have a 1..1 relationship. however in very-rare circumstances they do tend to have 1..n I need to update Table2.Field1 with Table1.Field1; In SQL Server we could write the following update statement to satisfy our business conditions. UPDATE Table2 SET Table2.Field1 = T1.Field1 FROM Table1 t1, Table2 t2 WHERE t1.Name = t2.Name; In oracle, we were having the below statement. UPDATE Table2 SET Field2 = (SELECT t1.FIELD1 FROM Table1 t1, Table2 t2 WHERE t1.Name = t2.Name); This statement works for Table1 having 1..1 relationship between Table1.Name and Table1.Field1 For 1..n relationship the ORACLE version fails with the following error - ORA-01427: single-row subquery returns more than one row The SQLServer sql works for both the cases 1..1 and 1..n; given the nature of the join. Could someone guide me with appropriate sql for the above issue? I am looking for a bulk update statement rather than using PL/SQL to go through each recrod in Table2. Thanks for your help. Kalyan

modified on Monday, June 7, 2010 10:55 AM

[Mycroft Holmes]

Fairly obvious, your sub select statement is returning 2 rows for n2, this is illegal. try putting DISTINCT in there

UPDATE Table2 SET Field2 = (SELECT DISTINCT t1.FIELD1 FROM Table1 t1, Table2 t2
WHERE t1.Name = t2.Name);

Never underestimate the power of human stupidity RAH

[Kalyan_A]

Thanks for your reply.
I do know that multiple values is what is causing this issue. Using DISTINCT would not work as the values being returned are distinct already. It would still return 2 rows for n2
This issue is due to ORACLE not supporting the same syntax as SQLSERVER. (as far as i know)
I am more comfortable with SQLServer than ORACLE and hence was checking if anyone else came across this issue. Kalyan

[Mycroft Holmes]

Sorry, looking at it from the wrong end. What do you expect the table2 field2 (missing from design given) to contain b or c and what does it contian when you use SQL Server, I'm betting tt has c. Does Oracle not support update with a join, try using INNER JOIN style instead of the tables in the where clause (horrible format) No matter how you work it your data structue sucks. the relationship is screwed and I consider Oracle correct in spitting the dummy.

Never underestimate the power of human stupidity RAH

[David Skelly]

I'm confused. Given the example data you have given above, what value would you expect to see in Table2.Field1 for row id 2? It could be either b or c. Which one do you want and how would you distinguish between them? If you don't care, then you could just say SELECT MAX(t1.FIELD1). That would give you one value. As far as I can see with your current SQL Server statement you have no control over which value goes in there, so I'm guessing it's not important to you.

[Kalyan_A]

you are right - (for now) its not important as to which value gets updated. The SQLServer version of the script simply updates the t2.Field with the last found record. So for 'n2' t2.Field1 will always end-up with value 'c' I was looking for an equivalent in ORACLE so that it will always update with the last row found. The actual issue is much bigger and i was looking for a quick-fix without introducing a major change. Looks like we need to address the issue with the current design before spending any further time on this Thanks for your inputs. Cheers Kalyan

[Kalyan_A]

you are right - (for now) its not important as to which value gets updated. The SQLServer version of the script simply updates the t2.Field with the last found record. So for 'n2' t2.Field1 will always end-up with value 'c' I was looking for an equivalent in ORACLE so that it will always update with the last row found. The actual issue is much bigger and i was looking for a quick-fix without introducing a major change. Looks like we need to address the issue with the current design before spending any further time on this issue, Thanks for your inputs. Cheers Kalyan