Updating a table from a join

let say deptno=10 is repeated twice in dept table with different department names and emp table has three employees working for deptno=10 then join between these two tables would produce six rows in the output, database has no clue about which department name should be updated here.Hence this error update is turned into a merge, then we don't see this error message. How do I know it's going to update row @to with data of @from and not the otherway around?Edit: Note: while I have not done extensive testing, this query does seem to work as I try it against our development database.MD_DT IS NOT NULL AND t1.unqid=t2.unqid AND t1.lt='hl' ) TEMP WHERE TEMP.ROW_ID=1) Newtwherenewt.unqid=t1.unqid; The select query that I shared completes in 13 seconds.

updating a table from a join-84updating a table from a join-41

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions.merge into emp using dept 2 on (emp.deptno = dept.deptno) 3 when matched then 4 update set ename = dname; merge into emp using dept * ERROR at line 1: ORA-30926: unable to get a stable set of rows in the source tables [email protected] only difference here is UPDATE does this check before execution, while MERGE does this as part of statement execution. MD_DT AS MD_DT, ROW_NUMBER() OVER (PARTITION BY t2.unqid ORDER BY t2.The right way to avoid this error is define either a primary key or a unique index/constraint on dept(deptno). MD_DT DESC) AS ROW_ID FROM t2, t1 WHERE t2.x = 'x' AND t2.y = 'y' AND t2.This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. By joining today you can post your own programming questions, respond to other developers questions, and eliminate the ads that are displayed to guests. I am able to select the rows I'd like to update but when I use that same basic logic to do an update it gives me some grief. I have the table I want to update some old data with new data(Table A) I have a table that contains a mapping between the old data and the new data (Table B) But I only want to update those rows in Table A that are a subset of Table C.So here's what I got -- the question is what am I missing: Update Table_A SET Table_A.current_id= (select Table_B.new_id from Table_B where Table_B.old_id = Table_A.current_id) INNER JOIN TABLE_C on Table_A.col1 = Table_C.col1 and Table_A.col2 = Table_C.col2 where Table_C.status = 'X' or Table_C.status = 'Y'; Any help would be appreciated.

Leave a Reply