Home » SQL & PL/SQL » SQL & PL/SQL » SQL Updating with non unique criteria [merged]
SQL Updating with non unique criteria [merged] [message #380230] Fri, 09 January 2009 11:31 Go to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Hi there,

Hoping someone can help me with this problem that I've been struggling with for a while now Mad

I have a master table, table1 and a temporary table table2.
I'm trying to populate some fields in table1 via a number of matching criteria as follows:

update table1
set field1 = (select field1
from table2
where table1.field2 = table2.field2
and table1.field3 = table2.field3
and table1.field4 = table2.field4
and table1.field5=table2.field5)

Unfortunately, there is no key linking the two tables which is why I'm having to do it this way. However, there are a number of cases when more than one row matches the where clauses. The rows, however, are not exact duplicates as they differ in a separate field, field6 say. Field6 is the primary key of table2. This query therefore results in the error "ORA-01427: single-row subquery returns more than one row".
I need to get all rows that match the where clause into table1.
I have tried a number of ways to get around this, including the "where exists" command, but without success? Any ideas very much appreciated!

Cheers,

F
Re: SQL Updating with non unique criteria [message #380231 is a reply to message #380230] Fri, 09 January 2009 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.

I am not clear on why this was posted Server Utilities subforum.

[Updated on: Fri, 09 January 2009 11:35]

Report message to a moderator

Re: SQL Updating with non unique criteria [message #380241 is a reply to message #380230] Fri, 09 January 2009 11:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
I need to get all rows that match the where clause into table1.

To paraphrase this, you want to put multiple values into one column in one row. Sorry, but this requirement does not logically make any sense. FIELD6 plays no role here if it is not present in TABLE1.
Re: SQL Updating with non unique criteria [message #380245 is a reply to message #380241] Fri, 09 January 2009 12:15 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Sorry, no. Fields 2-5 are matching criteria between the tables, i.e. for rows where these values match in both tables, I want to insert the value of field1, table2 into field1, table1 (currently null). Field6 just shows that the rows are not exact duplicates as it is a primary key. Hope this makes more sense?
Re: SQL Updating with non unique criteria [message #380246 is a reply to message #380245] Fri, 09 January 2009 12:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
frog9697 wrote on Fri, 09 January 2009 19:15
Hope this makes more sense?

It is still the same. OK. Imagine following data in TABLE1:
FIELD1 FIELD2 FIELD3 FIELD4 FIELD5
----------------------------------
  NULL      1      1      1      1
and in TABLE2:
FIELD1 FIELD2 FIELD3 FIELD4 FIELD5
----------------------------------
     3      1      1      1      1
     4      1      1      1      1
     5      1      1      1      1
How would you put values 3, 4, 5 into TABLE1.FIELD1 in that one row?
Re: SQL Updating with non unique criteria [message #380306 is a reply to message #380246] Sat, 10 January 2009 08:53 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Using the example you've provided, if we take the first row of table 2 we see that the entries in fields 2 to 5 (inclusive) are identical to those in table 1. Given this, I now want to insert the field1 value from table2 into table 1. So, given that all 3 rows of table 2 satisfy this, I want table 1 to look like this:

FIELD1 FIELD2 FIELD3 FIELD4 FIELD5
----------------------------------
3 1 1 1 1
4 1 1 1 1
5 1 1 1 1

Obviously this isn't a straightforward update query as we're not just simply updating exisiting rows. A more detailed example is attached as a text file.

I know this is a bit of a weird one, but unfortunately it's just the data I have, far from ideal Smile Not even sure if the above is possible?


  • Attachment: example.txt
    (Size: 0.70KB, Downloaded 74 times)

[Updated on: Sat, 10 January 2009 08:56]

Report message to a moderator

Re: SQL Updating with non unique criteria [merged] [message #380309 is a reply to message #380230] Sat, 10 January 2009 09:18 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I have no idea about scenarios, but I would not store that info into TABLE1 directly and use
SELECT NVL(table2.field1, table1.field1), table1.field2, table1.field3, table1.field4, table1.field5, table1.field6
FROM table1 LEFT JOIN table2 ON (table1.field2 = table2.field2
 AND table1.field3 = table2.field3
 AND table1.field4 = table2.field4
 AND table1.field5 = table2.field5);

If you insist of storing it, you may start from this query - move TABLE1 content and insert its result back.

[Edit: changed aliases in SELECT clause because of LEFT join]

Quote:
Field6 just shows that the rows are not exact duplicates as it is a primary key

After the required "not a straightforward update query", FIELD6 will no longer be unique. Are you aware of this?

[Edit:Added this quote]

[Updated on: Sat, 10 January 2009 09:33]

Report message to a moderator

Re: SQL Updating with non unique criteria [merged] [message #380312 is a reply to message #380309] Sat, 10 January 2009 09:49 Go to previous message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Ok great, thanks. I'll give it a go! I am aware that field6 will no longer be unique. I'm hoping that's not going to cause too many problems Smile
Previous Topic: Retrieve Column Name
Next Topic: what changes are to be made to sqlplus to show all the data on a single lin
Goto Forum:
  


Current Time: Sat Dec 03 05:38:30 CST 2016

Total time taken to generate the page: 0.12963 seconds