Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to update join.view when not using primary key

Posted on 2003-02-27
2
Medium Priority
?
463 Views
Last Modified: 2009-07-29
I want to execute the following query:

UPDATE A SET A.VALUE = A.VALUE + B.VALUE WHERE A.NOT_PRIMARY_KEY = B.NOT_PRIMARY_KEY;


For every row in a, there are multiple rows in b. I want to update every row in a that matches the criteria. How can I do this?

Example data:

Table A
NotPK   Value
1       1
2       2
3       3
3       4

Table B
NotPK   Value
1       1
2       2
2       3
3       3

Results:
Table A
NotPK   Value
1       2 (1+1)
2       7 (2+2+3)
3       6 (3+3)
3       7 (3+4)
0
Comment
Question by:covington
2 Comments
 
LVL 8

Accepted Solution

by:
Danielzt earned 400 total points
ID: 8036581

if you are in oracle, try this:

UPDATE A SET A.VALUE = A.VALUE + (select sum(B.VALUE)
 from B WHERE A.NOT_PRIMARY_KEY = B.NOT_PRIMARY_KEY );



0
 
LVL 3

Author Comment

by:covington
ID: 8036886
Perfect, thanks.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question