Level 55
@mikailfaruqali what you mean it not working? Error? Wrong result? No changes?
Apr 25, 2022
24
Level 1
Update Column value with subquery itself
hello everyone thanks for your replay i have average table and i want to update column based on previous rows. i was trying to do that with inner join but it not working
UPDATE `averages`
SET cost = (SELECT SUM(qty*cost)/SUM(qty) FROM averages averages_was
WHERE averages_was.order_number < averages.order_number
AND averages_was.product_id = averages.product_id
AND averages_was.branch_id = averages.branch_id)
Level 1
@SilenceBringer return ` ERROR 1093 (HY000): You can't specify target table 'averages' for update in FROM clause
Level 39
You use SUM (aggregate function) without GROUP BY
I suggest you to write /debug first your 'inner sql' and then write the update one.
Level 1
@sr57 bro can i update table column based on sum column previous rows
Level 39
Sorry @mikailfaruqali , I read your sql too quickly.
Yes you should be able to update the same table (with mysql & postgresql)
try changing
UPDATE `averages`
to
UPDATE averages
Level 39
Have you try this ?
UPDATE averages
SET cost = (SELECT SUM(qty*cost)/SUM(qty) FROM averages averages_was
WHERE averages_was.order_number < averages.order_number
AND averages_was.product_id = averages.product_id
AND averages_was.branch_id = averages.branch_id)
Level 1
@sr57 yes bro i tried this and give me this error
UPDATE averages
SET cost = (SELECT SUM(qty*cost)/SUM(qty) FROM averages averages_was
WHERE averages_was.order_number < averages.order_number
AND averages_was.product_id = averages.product_id
AND averages_was.branch_id = averages.branch_id)
error
SQLSTATE[HY000]: General error: 1093 You can't specify target table 'averages' for update in FROM clause (SQL: UPDATE averages SET cost = (SELECT SUM(qty*cost)/SUM(qty) FROM averages averages_was WHERE averages_was.order_number < averages.order_number AND averages_was.product_id = averages.product_id AND averages_was.branch_id = averages.branch_id))
Level 39
Which db / version are you using?
Level 39
Can you run this little test on your db
CREATE TABLE test (product text,qty integer, cost float);
UPDATE test SET cost = (SELECT SUM(qty*cost)/SUM(qty) FROM test t2 WHERE test.product=t2.product);
Level 1
@sr57 bro run in phpmyadmin give this error
#1093 - You can't specify target table 'test' for update in FROM clause
Level 39
Ok your db version does not allow this syntax.
1 like
Level 39
try this syntax (that's more correct -see my first answer)
UPDATE
test t1 INNER JOIN (SELECT product,SUM(qty*cost)/SUM(qty) AS new_cost FROM test GROUP BY product) t2 ON
(t1.product=t2.product)
SET t1.cost = t2.new_cost;
Level 1
@sr57 thanks bro befor post this question i tried INNER JOIN but this is not working fo me because i have more than one INNER JOIN and WHEN calculate each INNER JOIN have self condition so can get all record ,so i can't do it with INNER JOIN, i do this with laravel CHUNK() but it's too slow
Level 39
don't understand, you can have several JOINs
Level 39
Does the previous syntax work on your db?
Level 1
@sr57 yes bro it's worked ,sorry my english not good, bro i have two or more join in this table by type of record so each join have serval condition how i can do that ? one join not affects to another ,i used join only for SUM() of previous record
Level 39
You are welcome, English is also not my mother language.
As I wrote before step after step, close this first question and update your second post your new point ... and we'll see this tomorow. Now it's time to sleep (for me)
Level 1
@sr57 thanks bro good night have nice dream
Level 39
Is everything ok now?
Please or to participate in this conversation.