CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-update
errarr
errarr

Reputation: 3

SQL update field for every value connected with it

I have to create query, that raises client discount by 10 (adds 10 to previous value) for every order made by this client.

Database structure looks like this:

CLIENT(client_id, name, discount)
ORDER(id, client_id)


Anybody has any idea?

Upvotes: 0

Views: 47

Answers (3)

Vishal
Vishal

Reputation: 320

Sub query will be the best solution for such cases

UPDATE CLIENT SET CLIENT.DISCOUNT = CLIENT.DISCOUNT + 10 * (SELECT COUNT(*) FROM ORDER WHERE client_id = CLIENT.client_id)

Upvotes: 0

forpas
forpas

Reputation: 164099

I think this is what you need: 

UPDATE CLIENT c 
SET c.DISCOUNT = c.DISCOUNT + 10 * (SELECT COUNT(*) FROM ORDER WHERE client_id = c.client_id)

In this statement,

SELECT COUNT(*) FROM ORDER WHERE client_id = c.client_id

returns the number of orders placed by each client

Upvotes: 2

Oussail
Oussail

Reputation: 2288

UPDATE CLIENT SET DISCOUNT=10 WHERE client_id in (SELECT client_id from ORDER)

Upvotes: 0

Related Questions