Update using Select Statement

Question

I wanna write a query like this :

UPDATE `test_credit` 
SET `test_credit`.`credit`=(`test_credit`.`credit`-((`test_credit`.`credit`/100)*5)) 
WHERE `test_credit`.`name` = `users`.`uname`

in fact i want to get a query on users.uname = test_credit.name but mysql say it has error and realize users.uname as column

what is correct query ?

John Woo · Accepted Answer · 2013-04-07 04:52:09Z

4

You need to explicitly join it with table users. As far from my understanding based on your query, you want to calculate the credit if the names exists on both tables.

Give this a try,

UPDATE  test_credit a
        INNER JOIN users b
            ON a.name = b.uname
SET     a.credit = (a.credit - ((a.credit/100) * 5.0)) 
-- WHERE  b.parent= "example"

edited Apr 7, 2013 at 4:52

answered Apr 7, 2013 at 4:43

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Woo Over a year ago

if you have further questions, just drop a comment :)

user1718141 Over a year ago

tanx my friend it works and if i say a condition i should write like this? UPDATE test_credit a INNER JOIN users b ON a.name = b.uname AND b.parent= "example" SET a.credit = (a.credit - ((a.credit/100) * 5.0))

John Woo Over a year ago

since it is using INNER JOIN, you can add it on WHERE clause, see my update.

Collectives™ on Stack Overflow

Update using Select Statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related