Solved

Oracle update sql

Posted on 2013-05-28
6
465 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

739 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