Sql query update, customer with id=3 had his account balances increased by 10

Question

1.00/5 (2 votes)

See more:

customer table (CUST_ID,CUST_NAME,CUST_ADDRESS,EMP_ID)
has table (CUST_ID,ACCOUNT_NUMBER)
table account(ACCOUNT_NUMBER,ACCOUNT_BALANCE)

Customer with id=3 had his account balances increased by 10%

What I have tried:

SQL

UPDATE account
SET account.account_balance = account.account_balance * 1.1
WHERE EXISTS (SELECT a.account_number
             FROM account a,has h
             where a.account_number = h.account_number
             and h.cust_id=3)

IT IS UPDATING ALL ROWS I NEED TO UPDATE ONLY WHEN H.CUST_ID=3

Posted 2-Mar-20 15:29pm

Abed Al Rahman Hussien Balhawan

Updated 2-Mar-20 16:53pm

PIEBALDconsult

v4

Add a Solution

Comments

PIEBALDconsult 2-Mar-20 21:55pm

Was the help you receive before not helpful?
https://www.codeproject.com/Questions/5260382/Sql-query-to-be-solved-the-salary-of-employee-deal

Abed Al Rahman Hussien Balhawan 2-Mar-20 22:01pm

hello, i tried those queries and they did not work on oracle database10G

I tried this one on a similar case it works pretty fine , but here it us updating all rows, i do not understand why it is not reading the last statment
(((and h.cust_id=3)))

PIEBALDconsult 2-Mar-20 22:08pm

Please rest assures that it is doing exactly what you asked it to.

Abed Al Rahman Hussien Balhawan 2-Mar-20 22:14pm

i fixed it
UPDATE account
SET account.account_balance = account.account_balance * 1.1
WHERE EXISTS (SELECT h.account_number
FROM has h
where account.account_number = h.account_number
and h.cust_id=3)

PIEBALDconsult 2-Mar-20 22:08pm

EXISTS is a cry for help, don't use it, just use a JOIN (though I don't understand Oracle's JOIN syntax, bleah).

Abed Al Rahman Hussien Balhawan 2-Mar-20 22:15pm

I will try to do it with a inner join now.

PIEBALDconsult 2-Mar-20 22:25pm

Please do. Life will be easier.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2020-03-02T16:54:00

You really need to do some more research into the EXISTS operator, and use what you have learned earlier about using it versus a JOIN

SQL

UPDATE a
SET    a.Account_Balance = a.Account_Balance * 1.1
FROM   Account a
INNER JOIN Has h ON a.Account_Number = h.Account_Number
WHERE h.Cust_ID = 3

The beauty of using a JOIN statement for the UPDATE is the ability to pre-check it easily... simply comment out the UPDATE...SET lines in the above query and add in a simple SELECT like this to see what rows will be affected and before/after values; like this

SQL

--	UPDATE a
--	SET    a.Account_Balance = a.Account_Balance * 1.1
	SELECT h.Cust_ID, a.Account_Balance, NewBalance = a.Account_Balance * 1.1
	FROM   Account a
	INNER JOIN Has h ON a.Account_Number = h.Account_Number
	WHERE h.Cust_ID = 3

Abed Al Rahman Hussien Balhawan · Accepted Answer · 2020-03-02T16:15:00

Solution 1

UPDATE account
SET account.account_balance = account.account_balance * 1.1
WHERE EXISTS (SELECT h.account_number
             FROM has h
             where account.account_number = h.account_number
             and h.cust_id=3)

Posted 2-Mar-20 16:15pm