Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle update sql

Posted on 2013-05-28
6
Medium Priority
?
483 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 14

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
Industry Leaders: 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 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 78

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

876 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