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?
sochionnaitjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
HuaMin ChenProblem resolverCommented:
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

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Huaminchen,
The PO in the subquery is incorrect and must be removed
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Sanjeev LabhDatabase ConsultantCommented:
Can you provide some sample data from table A and B and also provide the expcted results after the update?
0
sochionnaitjAuthor Commented:
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
0
sventhanCommented:
Try this ...

update tablea set ref=
(select tableb.ur from tablea, tableb  where tablea.adm.tableb.uniqseq)
0
sochionnaitjAuthor Commented:
Hi, that update all the rows irrespective of whether the value in tablea.adm = the value in tableb.uniqseq
0
sventhanCommented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Update po A set col1= (select b.col2 from snp B where B.uniqseq = trim(substr(A.user,2,20))) where exists ( select b.col2 from snp B where B.uniqseq = trim(substr(A.user,2,20)))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sochionnaitjAuthor Commented:
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.
0
sochionnaitjAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Then please accept my comment(s) to grant the points.
Glad I could help.
0
Sanjeev LabhDatabase ConsultantCommented:
@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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.