Solved

Oracle update sql

Posted on 2013-05-28
6
454 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
6 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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]
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now