CODEWITHSUNDEEP
mysqlsql
action jack
action jack

Reputation: 53

Update with Nested Select queries

I've got so far with this with some searching on this site, and experimenting, but I can't find the last step. I have a SQL table of track_id and track_data. I want the latter to be updated from text pulled in from other tables via various joins. I can so far get it to display the data for one track_id I have in the test table. But I want to UPDATE the track_data with what I can display for every track_id. 

    track_id        track_data
    4560
    4561

My Select Query is

    SELECT * FROM (
    SELECT tdd.track_id, CONCAT(t.track_name, ' ',    t.track_description, ' ', t.keywords, ' ', tc.library_name, ' ', tc.label_name, ' ', tc.album_reference, ' ', 
    GROUP_CONCAT(category_name)) 
    FROM track_data AS tdd
    JOIN tracks t 
    ON tdd.track_id = t.track_id 
    JOIN tracks_cache tc 
    ON tc.track_id = t.track_id 
    JOIN categories_to_track ct 
    ON t.track_id = ct.track_id 
    JOIN categories c 
    ON c.category_id = ct.category_id
    ) AS trck

which outputs

    track_id        track_data
    4560            text text text from other tables categories etc

I want to UPDATE track_data with what I can view above, but also for every track_id in that table.

If I try adding SELECT trck.track_id at the start, it only shows me the one track id instead of all of them. And if I try wrapping it with 

   UPDATE track_data SET track_data = (
   SELECT CONCAT(t.track_name, ' ', t.track_description, ' ', t.keywords, ' ', tc.library_name, ' ', tc.label_name, ' ', tc.album_reference, ' ', 
   GROUP_CONCAT(category_name)) 
   FROM testtrackdata AS tdd
   JOIN tracks t 
   ON tdd.track_id = t.track_id 
   JOIN tracks_cache tc 
   ON tc.track_id = t.track_id 
   JOIN categories_to_track ct 
   ON t.track_id = ct.track_id 
   JOIN categories c 
   ON c.category_id = ct.category_id
   ) AS trck
   WHERE track_data.track_id = trck.track_id

and other variations, I just get SQL Syntax errors that I can't work out. I'm sure I'm nearly there, but I need some helpful person from this neck of the woods to help me over the finish line.

Many thanks

Upvotes: 1

Views: 55

Answers (1)

fancyPants
fancyPants

Reputation: 51888

In this query 

SELECT * FROM (
SELECT tdd.track_id, CONCAT(t.track_name, ' ',    t.track_description, ' ', t.keywords, ' ', tc.library_name, ' ', tc.label_name, ' ', tc.album_reference, ' ', 
GROUP_CONCAT(category_name)) 
FROM track_data AS tdd
JOIN tracks t 
ON tdd.track_id = t.track_id 
JOIN tracks_cache tc 
ON tc.track_id = t.track_id 
JOIN categories_to_track ct 
ON t.track_id = ct.track_id 
JOIN categories c 
ON c.category_id = ct.category_id
) AS trck

the outer query is not needed, you can assign an alias right there. Furthermore you're missing a GROUP BY if you want data for every track_id. So your query becomes this:

SELECT tdd.track_id, CONCAT(t.track_name, ' ',    t.track_description, ' ', t.keywords, ' ', tc.library_name, ' ', tc.label_name, ' ', tc.album_reference, ' ', 
GROUP_CONCAT(category_name)) AS track_data
FROM track_data AS tdd
JOIN tracks t 
ON tdd.track_id = t.track_id 
JOIN tracks_cache tc 
ON tc.track_id = t.track_id 
JOIN categories_to_track ct 
ON t.track_id = ct.track_id 
JOIN categories c 
ON c.category_id = ct.category_id
GROUP BY tdd.track_id

Note though, that this is not exactly right. The rule for using GROUP BY is, that every column you use in the SELECT clause should either be in the GROUP BY clause or an aggregate function should be applied on it. Reason is, that the rows for each group are kind of collapsed and when you don't apply an aggregate function, a random row for each group is displayed. MySQL allows this, but it's not really right. In your case you could apply the MAX() function on every column in your CONCAT() function.

Anyway, next problem will be, if you update the way you do it, you will get an error stating something like you can't read and update from the same table at the same time. You can prevent this by joining to your query instead of executing it in the SET section. This way MySQL produces a temporary table before actually updating.

UPDATE trackdata t 
JOIN (
    SELECT tdd.track_id, CONCAT(t.track_name, ' ',    t.track_description, ' ', t.keywords, ' ', tc.library_name, ' ', tc.label_name, ' ', tc.album_reference, ' ', 
    GROUP_CONCAT(category_name)) AS track_data
    FROM track_data AS tdd
    JOIN tracks t 
    ON tdd.track_id = t.track_id 
    JOIN tracks_cache tc 
    ON tc.track_id = t.track_id 
    JOIN categories_to_track ct 
    ON t.track_id = ct.track_id 
    JOIN categories c 
    ON c.category_id = ct.category_id
    GROUP BY tdd.track_id
) sq ON t.track_id = sq.track_id
SET t.track_data = sq.track_data;

Note though that I'm still too lazy to apply above mentioned aggregate functions on your columns :)

Upvotes: 1

Related Questions