Update multiple columns with a SELECT statement

[maeC]

I have a list of playlists that has different start date and end date. It's a playlists that changes every 3 days or so. I have a code that calculate the number of exposures of that certain playlist. Since, the dates changes constantly and my code for the exposures is based on the dates, I created a table where if the client publish a different playlist, the number of total exposures taken by the previous playlist will be saved in that table which is the playlist_exposu res.

What I did was, my code checks if the playlist_id already exists in playlist_exposu re, if it doesn't, I have a code that will just insert all data needed but if the id already exists, all I need is just to update the column(current_ exposures) and add the new total exposures..

I have no problem in inserting data but I don't know how to update it.

Here's a sample of my select statement:

SELECT (((SUM(total_uptime.total_on_hours))*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM total_uptime
        JOIN playlist_terminal
        ON total_uptime.terminal_id = playlist_terminal.terminal_id
        JOIN taxi
        ON taxi.terminal_id = playlist_terminal.terminal_id
        JOIN playlists
        ON playlists.playlist_id = '608'
        WHERE playlist_terminal.userid = '10' AND playlists.userid = '10' AND total_uptime.start_time BETWEEN  '2014-09-03' AND '2014-09-05'
        GROUP BY playlist_terminal.ref_number

and my idea of updating the table is:

UPDATE playlist_exposures set current_exposure = (SELECT (((SUM(total_uptime.total_on_hours))*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM total_uptime
        JOIN playlist_terminal
        ON total_uptime.terminal_id = playlist_terminal.terminal_id
        JOIN taxi
        ON taxi.terminal_id = playlist_terminal.terminal_id
        JOIN playlists
        ON playlists.playlist_id = '608'
        WHERE playlist_terminal.userid = '10' AND playlists.userid = '10' AND total_uptime.start_time BETWEEN  '2014-09-03' AND '2014-09-05'
        GROUP BY playlist_terminal.ref_number)
WHERE playlist_id = '608'

I already tried this but unfortunately, it doesn't work.

If anyone could help me, give suggestions or at least guide me. It would be a big help and I would really appreciate it. Thank you in advance!

Cheers!

[Gobindap]

Did you try this? Instead to use select statement directly in sql statement, return a value from select statement first and then use the value in update statement.

I think it really works.

[maeC]

I don't know why I didn't thought of that. Thank you for the suggestion.