By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,151 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Update multiple columns with a SELECT statement

P: 5
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_exposures.

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
  1. SELECT (((SUM(total_uptime.total_on_hours))*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM total_uptime
  2.         JOIN playlist_terminal
  3.         ON total_uptime.terminal_id = playlist_terminal.terminal_id
  4.         JOIN taxi
  5.         ON taxi.terminal_id = playlist_terminal.terminal_id
  6.         JOIN playlists
  7.         ON playlists.playlist_id = '608'
  8.         WHERE playlist_terminal.userid = '10' AND playlists.userid = '10' AND total_uptime.start_time BETWEEN  '2014-09-03' AND '2014-09-05'
  9.         GROUP BY playlist_terminal.ref_number
and my idea of updating the table is:

Expand|Select|Wrap|Line Numbers
  1. 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
  2.         JOIN playlist_terminal
  3.         ON total_uptime.terminal_id = playlist_terminal.terminal_id
  4.         JOIN taxi
  5.         ON taxi.terminal_id = playlist_terminal.terminal_id
  6.         JOIN playlists
  7.         ON playlists.playlist_id = '608'
  8.         WHERE playlist_terminal.userid = '10' AND playlists.userid = '10' AND total_uptime.start_time BETWEEN  '2014-09-03' AND '2014-09-05'
  9.         GROUP BY playlist_terminal.ref_number)
  10. 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!
Sep 15 '14 #1
Share this Question
Share on Google+
2 Replies


Gobindap
P: 25
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.
Sep 16 '14 #2

P: 5
I don't know why I didn't thought of that. Thank you for the suggestion.
Sep 16 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.