What I did was, my code checks if the playlist_id already exists in playlist_exposure, 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:
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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'
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!