CODEWITHSUNDEEP
mysqlsqldatabase
KMarto
KMarto

Reputation: 300

How to update a column within a query in mysql

I want to update a column using aggregate function but I don't want to loop through each row. I'm coming from SQL Server where we do something like 

With CTE as (select name, price, cost,  quantity price*quantity as total)
update CTE 
   set cost = total

With this I am able to update whole table without looping through every record. How can I accomplish the same task in mysql

Upvotes: 0

Views: 59

Answers (2)

apokryfos
apokryfos

Reputation: 40683

My suggestion is to not store cost column at all. Instead create a view:

CREATE VIEW sales_with_cost 
AS 
select name, price, cost,  quantity, price*quantity as total FROM sales

You can use sales_with_cost instead of sales directly and can ensure that your data is consistent without needing to do an update.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Just do computation :

update sales 
    set cost = price * quantity
where . . . ;

However, the same would be work with SQL Server no need to use updateble CTE :

Upvotes: 1

Related Questions