asked on

ORACLE SQL UPDATE Column in table A value from Table B

Hi I have 2 tables on Oracle, I wish to update one column in table A with a value from table b, when a substring of a column value in table A is equal to a value in a column on table b

The following query tells me that there are 3 cases where this occurs:

select A.C1, B.uniqseq, B.ur, B.ad from snp B, PO A where B.uniqseq = trim(substr(A.user,2,20))

I now want to update a column in table A with the values returned in the above query. What syntax should I be using?

Guy Hengel [angelIII / a3]

http://mobile.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
TThe article explains how to do

Peter Chan

Try

update po set col1=
(select b.col2 from snp B, PO A where B.uniqseq = trim(substr(A.user,2,20)))
where ...;

Open in new window

Guy Hengel [angelIII / a3]

Huaminchen,
The PO in the subquery is incorrect and must be removed

Sanjeev Labh

Can you provide some sample data from table A and B and also provide the expcted results after the update?

sochionnaitj

ASKER

Hi Sanjeev,

TABLE A
"ORDER"        "REF"       "DATE"        "Adm"
"000056266"      ""      20140806      "6129121287675"

Table B
"ORD_NO"        "UNIQSEQ"        "UR"        "ADMISSION"
"000056266"      "6129121287675"      "1010439"      "6129121 "

I want to be able to update the "REF" column on Table A with the "UR" value from Table B WHERE the "ADM" value in Table A is the same as "UNIQSEQ" value in table B

sventhan

Try this ...

update tablea set ref=
(select tableb.ur from tablea, tableb where tablea.adm.tableb.uniqseq)

sochionnaitj

ASKER

Hi, that update all the rows irrespective of whether the value in tablea.adm = the value in tableb.uniqseq

sventhan

This is a little clear version. If this does not work you've to add more keys ...

update t
set ref = b.ur
from tablea t
join tableb s
on t.adm = s.uniqseq

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sochionnaitj

ASKER

Thanks to All.

Guy your solution above was perfect and did exactly what I needed. I'm new to this so may be back. Thanks again.

sochionnaitj

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for sochionnaitj's comment #a40245393

for the following reason:

The solution provided by Guy did exactly what I required.

Guy Hengel [angelIII / a3]

Then please accept my comment(s) to grant the points.
Glad I could help.

Sanjeev Labh

@sochionnaitj - It always helps to provide sample data. In future whenever you put similiar questions do make sure to put sample data. Resolution comes faster.