imonfireDAMMIT
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...
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 :-)
My tables look like this...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I point to the comment immediately above
(i.e. is it commit related? 'cause that sql looks fine)
(i.e. is it commit related? 'cause that sql looks fine)
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 :-)
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 :-)
>> 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.
I believe you will find it much more efficient than the update you have.
-> update in session A (no commit)
-> trying to select the "new" state in session B (old state)