57242

MySQL combine UPDATE and SELECT query

Question:

I have the following SELECT statement that returns data, example below:

SELECT performers.ID, performers.Name, COUNT(*) AS CountOfDeals, COUNT(DISTINCT(deals.Name)) AS CountOfAliases FROM deals RIGHT JOIN performers ON deals.name LIKE CONCAT('%', performers.name, '%') WHERE performers.ID IN ( 27952, 27951, 27950, 27949, 27948 ) GROUP BY Name;

Example data returned:

ID Name CountOfDeals CountOfAliases 27952 Christine Hoberg 1 0 27951 Indian Jewelry 1 0 27952 Kinky Friedman 5 3 27949 KJ-52 1 0 27960 River Whyless 1 0

I want to combine this with the following UPDATE statement

UPDATE performers SET RawAliasCount = CountOfAliases, RawDealCount = CountOfDeals WHERE ID = ?

All the values needed to run the update statement are returned in the select statement above so hopefully this should be pretty easy.

Thanks.

Answer1:

Use update with join:

UPDATE performers p JOIN (SELECT performers.ID, performers.Name, COUNT(*) AS CountOfDeals, COUNT(DISTINCT(deals.Name)) AS CountOfAliases FROM deals RIGHT JOIN performers on deals.name LIKE CONCAT('%', performers.name, '%') WHERE performers.ID IN (27952, 27951, 27950, 27949, 27948) GROUP BY Name ) pp ON pp.id = p.id SET RawAliasCount = pp.CountOfAliases, RawDealCount = pp.CountOfDeals;

Answer2:

UPDATE performers SET performers.RawAliasCount = count_table.CountOfAliases, performers.RawDealCount = count_table.CountOfDeals FROM performers INNER JOIN ( SELECT performers.ID, performers.Name, COUNT(*) AS CountOfDeals, COUNT(DISTINCT(deals.Name)) AS CountOfAliases FROM deals RIGHT JOIN performers on deals.name LIKE CONCAT('%', performers.name, '%') WHERE performers.ID IN (27952, 27951, 27950, 27949, 27948) GROUP BY Name ) count_table ON count_table.ID = performers.ID;

When this type of question is asked, thank you to put the tables schema.

edit : sorry, it's sql-server syntax.

Recommend

  • Using :data attribute as a selector to filter elements
  • ClojureScript zipmap tricks me or what?
  • my sql multiple category-subcategory-subcategory
  • Regular Expression for decimal numbers
  • How to deduce changing table variable using jQuery
  • Java How to get time printed in IST
  • Parse elements with the same name?
  • uppercase issue for title in wordpress
  • Timezone display value with GMT offset from a given timezone?
  • Wierd Results using script to find length of a string?
  • How to use pointers to return 2D arrays functions?
  • Addresses of Delphi and C++ WinAPI functions differ when they shouldn't
  • How to redirect after form post with scrapy_splash package?
  • Boost Spirit Qi Attribute Propagation
  • ES6 in NodeJS: Arrow functions in object literal, what is the returned 'this' value? [dupl
  • Check through an ArrayList if it contains a String. If it doesn't, add that String
  • How to pass a custom object in REST webservice
  • Will WaitForMultipleObjects modify the state of *multiple* objects? [duplicate]
  • How to form PHP curl request for the following command line?
  • How can I include javascript in content loaded with jquery using $.ajax
  • Converting Twitter DateTime to Localtime with adding UTC offset [duplicate]
  • date.js Parse method overrides Javascript Parse method
  • Closing thread using ExitThread - C
  • Redirect response to download file
  • How to run requests.get asynchronously in Python 3 using asyncio?
  • String encoding problem on PdoStatement->bindParam()?
  • getElementsByClassName and AJAX
  • Ruby: Why does this way of using map throw an error?
  • Parse Framework with Swift
  • How to use the resource module to measure the running time of a function?
  • How to open multiple instances of a program in Linux
  • TSQL Rolling Average of Time Groupings
  • Find group of records that match multiple values
  • Ubuntu and bcrypt
  • Using JRuby with Rails 3.2
  • Unable to install Git-core+svn by MacPorts
  • Caching attributes in superclass
  • Is it possible to access block's scope in method?
  • SignalR .NET Client Invoke throws an exception
  • WPF Applying a trigger on binding failure