Updating only the joined rows

Question

I am trying to understand the update statement in SQL. I have tried many different ways of doing the work but nothing seems to be working. I have to join to table and ONLY on the joined rows, I have update a column text from "and" to "is".

This is exactly what I am trying to do. I hope this is making sense:

update (select t2.text from rules t1 inner join rules t2 on t1.parentid = t2.childid
where t1.parentid > 0 and 
t2.value = to_char (t1.position))
set text = replace(text, 'and', 'is');

I would really appreciate all your help.

Define "not working". Are you getting an error? If so, what error? Is the statement updating more rows than you expect? Or fewer? Which rows are/ are not being updated? My first thought would be that you're getting an error saying that the join is not key preserved but that's just a guess. — Justin Cave, Mar 25 '14 at 20:16
I removed the sql-server tag because the question is explicitly about PL/SQL. — Gordon Linoff, Mar 25 '14 at 20:17
Check this: might have what you want http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join — user2726995, Mar 25 '14 at 20:19
why do you have `table` keyword after `from` and `join` there are not needed, even wrong. `SELECT * FROM table_name INNER JOIN table_name2` — CodeBird, Mar 25 '14 at 20:22
Given the syntax issues in the SELECT statement, I'm curious - what are the names of the tables you're trying to join? — Bob Jarvis - Слава Україні, Mar 25 '14 at 20:46

Gordon Linoff · Accepted Answer · 2014-03-25T20:32:25.380

2

If I have the logic correct, you want to do the replacement in the first table, when the id exists in the second table. Use a where clause instead:

update table1 
    set text = replace(text, 'is', 'and')
    where exists (select 1
                  from table2 t2
                  where t2.id = table1.id
                 );

I am assuming that the condition t2.id > 0 is a redundant way of specifying a match. Because the two ids are the same, I would use table1.id > 0:

update table1 
    set text = replace(text, 'is', 'and')
    where id > 0 and
          exists (select 1
                  from table2 t2
                  where t2.id = table1.id
                 );

edited Mar 25 '14 at 20:32

answered Mar 25 '14 at 20:19

Gordon Linoff

1,242,037
58
646
786

@Justin - yes, you are right. The key preserved is an error I am getting. – datacentric Mar 25 '14 at 20:30
@Gordon - Thanks. Sorry for the extra tag – datacentric Mar 25 '14 at 20:30
@user2726995 - Thanks for the link but this is different than what I am looking for. – datacentric Mar 25 '14 at 20:31
@GriffeyDog . . . I sort of assumed that that condition is unnecessary, but I updated the answer anyway. – Gordon Linoff Mar 25 '14 at 20:32
@Gordon - Thanks for the query. Here is a more detailed explanation for what I am looking for - I have to join the same table. Table with Table. This is how the table is structured. Two rows within the same table can be joined together based on the same id (in different columns.. something like parent id and child id). After joining, I only have to update the text in the joined rows. I hope this is making sense. I can explain in more detail if needed. – datacentric Mar 25 '14 at 20:33
1

@datacentric Your last comment is a part of a question. So, please add it to question text. – ThinkJet Mar 25 '14 at 21:31

score 2 · Answer 2 · answered Mar 25 '14 at 20:34

2

first problem you have, is that you're using table keyword in your select this is a wrong syntax.

Second problem is that you're telling oracle set t1.text when your query result, the select doesn't have t1.text but has text

this query works:

update (select t1.text from t1 inner join t2 
on t1.id = t2.id
where t2.id > 0)
set text = replace(text_val, 'is', 'and');

Here's a working fiddle

answered Mar 25 '14 at 20:34

CodeBird

3,883
2
20
35

This is exactly what I need to do but this is the error I am getting:ORA-01779: cannot modify a column which maps to a non key-preserved table – datacentric Mar 25 '14 at 20:50
id should be `primary key` in both tables, @datacentric – CodeBird Mar 25 '14 at 20:51
id is the primary key. This is what is confusing to me. I modified my original query based on your answer. I included that in my original quesiton – datacentric Mar 25 '14 at 20:59
can I see your final query, the one that is giving you this error? @datacentric – CodeBird Mar 25 '14 at 21:04
I included that in my original question: Here it is again:update (select t2.text from rules t1 inner join rules t2 on t1.parentid = t2.childid where t1.parentid > 0 and t2.value = to_char (t1.position)) set text = replace(text, 'and', 'is'); – datacentric Mar 25 '14 at 21:11
@datacentric I don't know something is wrong with your keys, the only way I can help is by seeing your data. try creating a fiddle let's see what's going on. – CodeBird Mar 25 '14 at 21:16
Thanks much CodeBird. Sorry but I cant share the data. All I know I that both the tables have the primary key as listed in the query. Is there a way I can do it without joining the tables, so there is no issue of primary key? – datacentric Mar 25 '14 at 21:19
@datacentric you'll have to use the solution of Gordon... which is logically better. – CodeBird Mar 25 '14 at 21:28

score 1 · Answer 3 · answered Mar 25 '14 at 21:29

It's possible to use MERGE statement for that purpose.

If you have a table with child-parent relationship defined like that:

create table t1 (
  id         number, 
  parent_id  number,
  text_field varchar2(100)
)

you can find all matched pairs and then search for records to update with it's unique identifiers:

merge into t1 target_t 
using (
  select 
    parent_tab.id  parent_id,
    child_tab.id   child_id
  from 
    t1 parent_tab,
    t1 child_tab
  where 
    parent_tab.id = child_tab.parent_id

) found_records 
on (
  target_t.id in (found_records.parent_id, found_records.child_id) 
)
when matched 
  then update
    set 
     target_t.text_field = replace(target_t.text_field, 'and', 'is')
;

SQLFiddle

This worked: update (select text from rules t1 where exists (select childid from rules t2 where t1.parentid = t2.childid and t1.value = to_char(t2.position))) set text = replace(text, 'and', 'is'); Thank you all for your help! you guys are wonderful! — datacentric, Mar 25 '14 at 22:05

Updating only the joined rows

3 Answers3