Oracle update sql

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 :-)
LVL 1
imonfireDAMMITAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
PaulCommented:
I point to the comment immediately above
(i.e. is it commit related? 'cause that sql looks fine)
0
 
imonfireDAMMITAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.