CODEWITHSUNDEEP
mysqlsqlsubquerysql-update
Rohit Banga
Rohit Banga

Reputation: 18926

MySQL reference result from subquery

This is what i am doing

update t1 set x=a,y=b where a and b are obtained from (select query here)
  1. I know the select query
  2. The select query returns multiple results which are the same
  3. When I use group by or distinct query execution slows down considerably
  4. a and b are forward references so mysql reports an error
  5. I want to set a equal to the value obtained in the first row and b equal to the value obtained in the first row for the respective columns, to avoid group by. I don't know how to refer to the first result from the select query.

How can i achieve all this?

Upvotes: 0

Views: 1287

Answers (2)

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102438

LIMIT specifies the number of rows to return from the beginning of the result set:

SELECT * FROM t2 LIMIT 1; # Retrieve 1st row

LIMIT in your case is applied in the subquery in your from clause.

These linsk can help you out with update that uses a subquery:

Update with Subquery

Subqueries in MySQL, Part 1

Upvotes: 1

DRapp
DRapp

Reputation: 48169

you might be looking for something like...

update T1, (select Sub1.a, Sub1.b from YourSubQueryTable Sub1 where ... ) SubResult
  set T1.a = SubResult.a,
      T1.b = SubResult.b
  where
      Some T1.Criteria To be applied

Upvotes: 0

Related Questions