CODEWITHSUNDEEP
mysql
psr
psr

Reputation: 25

Update multiple rows in table in mysql

I am new to learning mysql. I have storedata table having 3 columns

 - id     -price     -url

and luckyvitamin table having 2 columns 

-id       -price   -date

I want to update the storedata price values using luckyvitamin.

I am using below query. Please solve my problem.
Thankyou.

update storedata s, luckyvitamin l 
set s.cost = (
    select l1.otcdeal_price 
    from store_data st,luckyvitamin lu  
    where st.id=lu.id
) 
where s.id=l.id;

Upvotes: 0

Views: 130

Answers (3)

Nishant Saini
Nishant Saini

Reputation: 431

UPDATE storedata s
INNER JOIN luckyvitamin l
ON s.id = l.id 
SET s.price = l.price

Upvotes: 0

tom nguyen
tom nguyen

Reputation: 316

You can write like this:

update storedata s inner join luckyvitamin l on s.id=l.id
set s.cost= l.otcdeal_price;

Upvotes: 0

Fabio
Fabio

Reputation: 23480

I think you can use an INNER JOIN query to achieve this, you don't need any subquery which will make the query itself slower

UPDATE storedata s
INNER JOIN luckyvitamin l
ON s.id = l.id
SET s.cost = l.otcdeal_pric

Upvotes: 2

Related Questions