[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 876
  • Last Modified:

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?
0
sochionnaitj
Asked:
sochionnaitj
  • 4
  • 4
  • 2
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
HuaMinChenBusiness AnalystCommented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Sanjeev LabhCommented:
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
 
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 LabhCommented:
@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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now