CODEWITHSUNDEEP
mysqlsql
Roxerg
Roxerg

Reputation: 116

Select from table, update the rows selected

I am trying to write a query that selects values from certain rows based on their parameters, then does some calculations and returns the result. I also need to update the rows that were selected. I can only think of ways to do both of these actions with separate queries, but is there a way to do both at once?

Example queries would be:

SELECT SUM(a.val1*b.val1) 
FROM a, b 
WHERE a.val2 = condition1 AND b.val2 = condition2;

UPDATE a
SET a.val3 = a.val1*b.val1
FROM a INNER JOIN b ON a.val2 = condition1 AND b.val2 = condition2;

Is there a way to combine them?

Upvotes: 1

Views: 71

Answers (3)

Peace_Man
Peace_Man

Reputation: 45

This will have to be done in two steps - Select and Aggregate then Update.

Upvotes: 1

Tim Rogers
Tim Rogers

Reputation: 496

No. There is no syntax in SQL that allows you to retrieve values and update them in the same query. Use SELECT to retrieve values and UPDATE to change them.

You can use SELECT inside an UPDATE statement as part of the calucalation, but the result will not be the values of what has been updated, only the number of rows that were updated.

Your SELECT statement has a small mistake in it and should be as follows:

SELECT SUM(a.val1*b.val1) FROM a, b WHERE a.val2 = 1 and b.val2 = 1;

This only returns one row: the sum of the product of val1 columns in tables a and b where the val2 columns meet certain conditions.

It is not clear what you are trying to achieve by updating table a with the result of this. If you are looking to set val3 in table a with the product of val1 in tables a and b if the val2 in those tables meet certain criteria, the following might work, but you need to add a join between columns in both tables otherwise val3 will be set to the product of val1 in table a and all the val1 values in table b, which may not be what you want.

UPDATE a
SET a.val3 = 
(
SELECT a.val1*b.val1 
FROM b
WHERE b.key = a.key
AND b.val2 = condition2
)
WHERE a.val2 = condition1;

Upvotes: 1

Alex
Alex

Reputation: 741

Nope :) In SQL it is always different queries. You can write a function that will do 2 actions, but never one query.

Upvotes: 1

Related Questions