Link to home
Start Free TrialLog in
Avatar of imonfireDAMMIT
imonfireDAMMITFlag for United States of America

asked on

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...

User generated image
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 :-)
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
I point to the comment immediately above
(i.e. is it commit related? 'cause that sql looks fine)
Avatar of imonfireDAMMIT

ASKER

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 :-)
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>> 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.