Solved

Oracle update sql

Posted on 2013-05-28
6
470 Views
Last Modified: 2013-05-29
I am trying to update my Table2 Device column, with the values from Table1 Device Column.  ID column is unique ID.

My tables look like this...

Table Example
My update statement is this:

Update Table2
        Set Table2.DEVICE =
                            (Select Table1.DEVICE From Table1
                                       WHERE Table1.ID = Table2.ID)

Seems pretty straightforward, yet NO ROWS are updating.

Any pointers would be awesome :-)
0
Comment
Question by:imonfireDAMMIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 39202132
Works for me

drop table tab1 purge;
create table tab1(id number, device char(1));

drop table tab2 purge;
create table tab2(id number, device char(1));

insert into tab1 values(111,'A');
insert into tab1 values(222,'B');
insert into tab1 values(333,'B');
insert into tab1 values(444,'A');

insert into tab2 values(111,null);
insert into tab2 values(222,null);
insert into tab2 values(333,null);
insert into tab2 values(444,null);
commit;

update tab2 set device=(select device from tab1 where tab1.id=tab2.id);

select * from tab2;

Open in new window

0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39202141
If you are using 11g, you might also check out MERGE with a 'when matched' only:

merge into tab2 t2
using (
      select * from tab1
) t1
on ( t1.id = t2.id )
when matched then
    update set t2.device = t1.device
;


Prior to 11g, you also need an INSERT clause for MERGE.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39202217
Don't get me wrong, but do you have committed the transaction (this happens quite often)?!
-> update in session A (no commit)
-> trying to select the "new" state in session B (old state)
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39203462
I point to the comment immediately above
(i.e. is it commit related? 'cause that sql looks fine)
0
 
LVL 1

Author Closing Comment

by:imonfireDAMMIT
ID: 39204270
I figured out the issue. I didn't give the actual tables/columns that I was using, as I was trying to simplify it for the sake of anyone helping out. My where criteria had four different statements. I ended up doing something like this:

Update table2
   Set device =
       (select device from table1
           where table1.ID = table2.ID
               and table1.is_load = 'Y'
               and table1.source = 'Equipment'
               and table2.version <> void)
where table2.id =
     (Select table1.id from table 1
       where table1.ID = table2.ID
               and table1.is_load = 'Y'
               and table1.source = 'Equipment'
               and table2.version <> void)

I needed it to only update the rows specified, without the additional where clause, all the rows were getting updated if they met the criteria in the first select statement. By adding the second select statement it seemed to only update what I wanted. I spent hours trying to get this right.

I also experimented with the Merge and that seemed to work great. I have never used merge before so I need to do some more research on it and testing but in the future I may go that route.

Thanks for the help guys :-)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39204287
>> I have never used merge before so I need to do some more research on it and testing but in the future I may go that route.

I believe you will find it much more efficient than the update you have.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question