SELECT in UPDATE statement
-
Hello All, I have a problem that I cannot seem to solve. My guess is that I am thinking wrong. The problem is probably really simple but I cannot seem to solve it. I have two tables, one called test_user and one called test_answer. (Below all types in the tables are integers) test_user looks like this: user_id,time_answered,+some other statistics 1 0 2 0 3 0 test_answer looks like this: test_id,user_id,answered 0 1 1 0 2 1 0 3 0 1 1 1 1 2 1 1 3 0 3 1 1 3 2 0 3 3 0 Basicly test_answered contains a 1 in the answered if the user has answered a question on test test_id. What I want to do is sum up all the answers and update the test_user table. If I do this SQL-query then I get the table I want to update test_user with. select user_id,sum(answered) as sum from test_answer where answered = 1 group by user_id (The output will be: user_id,sum 1 3 2 2 ) Is there a simple way to update the test_user tables without doing a select into (and destroying all other data I have in the table.) As I told you this is probably an super simple question but I cannot get it right? hope you guys can help me!
-
Hello All, I have a problem that I cannot seem to solve. My guess is that I am thinking wrong. The problem is probably really simple but I cannot seem to solve it. I have two tables, one called test_user and one called test_answer. (Below all types in the tables are integers) test_user looks like this: user_id,time_answered,+some other statistics 1 0 2 0 3 0 test_answer looks like this: test_id,user_id,answered 0 1 1 0 2 1 0 3 0 1 1 1 1 2 1 1 3 0 3 1 1 3 2 0 3 3 0 Basicly test_answered contains a 1 in the answered if the user has answered a question on test test_id. What I want to do is sum up all the answers and update the test_user table. If I do this SQL-query then I get the table I want to update test_user with. select user_id,sum(answered) as sum from test_answer where answered = 1 group by user_id (The output will be: user_id,sum 1 3 2 2 ) Is there a simple way to update the test_user tables without doing a select into (and destroying all other data I have in the table.) As I told you this is probably an super simple question but I cannot get it right? hope you guys can help me!
What do you mean it is destroying all the other data in the table? What is your existing UPDATE query?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
-
What do you mean it is destroying all the other data in the table? What is your existing UPDATE query?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
-
Hello All, I have a problem that I cannot seem to solve. My guess is that I am thinking wrong. The problem is probably really simple but I cannot seem to solve it. I have two tables, one called test_user and one called test_answer. (Below all types in the tables are integers) test_user looks like this: user_id,time_answered,+some other statistics 1 0 2 0 3 0 test_answer looks like this: test_id,user_id,answered 0 1 1 0 2 1 0 3 0 1 1 1 1 2 1 1 3 0 3 1 1 3 2 0 3 3 0 Basicly test_answered contains a 1 in the answered if the user has answered a question on test test_id. What I want to do is sum up all the answers and update the test_user table. If I do this SQL-query then I get the table I want to update test_user with. select user_id,sum(answered) as sum from test_answer where answered = 1 group by user_id (The output will be: user_id,sum 1 3 2 2 ) Is there a simple way to update the test_user tables without doing a select into (and destroying all other data I have in the table.) As I told you this is probably an super simple question but I cannot get it right? hope you guys can help me!
UPDATE test_user
SET no_answered = SUM(answered)
FROM test_answer
WHERE user_id = test_answer.user_idI haven't tested this, but it's along these lines. Stability. What an interesting concept. -- Chris Maunder
-
Hello All, I have a problem that I cannot seem to solve. My guess is that I am thinking wrong. The problem is probably really simple but I cannot seem to solve it. I have two tables, one called test_user and one called test_answer. (Below all types in the tables are integers) test_user looks like this: user_id,time_answered,+some other statistics 1 0 2 0 3 0 test_answer looks like this: test_id,user_id,answered 0 1 1 0 2 1 0 3 0 1 1 1 1 2 1 1 3 0 3 1 1 3 2 0 3 3 0 Basicly test_answered contains a 1 in the answered if the user has answered a question on test test_id. What I want to do is sum up all the answers and update the test_user table. If I do this SQL-query then I get the table I want to update test_user with. select user_id,sum(answered) as sum from test_answer where answered = 1 group by user_id (The output will be: user_id,sum 1 3 2 2 ) Is there a simple way to update the test_user tables without doing a select into (and destroying all other data I have in the table.) As I told you this is probably an super simple question but I cannot get it right? hope you guys can help me!
I'm not particularly sure that this is what you want. You want to update or insert into the test_user table depending on the records in test_answer, right? If that is the case, this might be the answer:
INSERT INTO test_user (user_id, time_answered)
(
SELECT user_id, SUM(answered)
FROM test_answer
WHERE answered = 1 AND user_id NOT IN (SELECT user_id FROM test_user)
GROUP BY user_id
);UPDATE test_user
SET time_answered = SUM(answered)
FROM test_answer
WHERE test_answer.user_id = test_user.user_id;