SQL Updating with non unique criteria [merged] [message #380230] |
Fri, 09 January 2009 11:31  |
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
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 #380241 is a reply to message #380230] |
Fri, 09 January 2009 11:58   |
flyboy
Messages: 1903 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   |
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   |
flyboy
Messages: 1903 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   |
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 Not even sure if the above is possible?
-
Attachment: example.txt
(Size: 0.70KB, Downloaded 620 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   |
flyboy
Messages: 1903 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
|
|
|
|