Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Posted by KnowledgeWoods Consulting
KnowledgeWoods Consulting
5,607 Points

Update Row in MS SQL

I have a table which stores userid, percentage and topics completed. I want that when userid is same it does not create a new row and update percentage and topics completed on same row.

My code is below:

$kwsql="IF EXISTS (SELECT FROM completion_tracker_pmp WHERE userid = 9)
    UPDATE completion_tracker_pmp SET (percentage='$val', topics_completed='$val2') WHERE userid=9;
ELSE
    INSERT INTO completion_tracker_pmp(userid,percentage,topics_completed)  VALUES(9,'$val','$val2')";

$kwresult = $kwdb->query($kwsql);

It is not working correctly. Please Help.

I am USING MS SQL.

2 Answers

Jeremy Menicucci
Jeremy Menicucci
19,227 Points
Jeremy Menicucci
Jeremy Menicucci
19,227 Points

Oh, my mistake, Bhaskar, I totally wasn't paying attention.

In that case it may have been a missing asterisk after the SELECT statement, but this worked on sql fiddle for me just in case,

IF EXISTS (SELECT * FROM completion_tracker_pmp WHERE userid = 9)
BEGIN
   UPDATE completion_tracker_pmp SET percentage ='$val', topics_completed ='$val2' WHERE userid=9
END
ELSE
BEGIN
   INSERT completion_tracker_pmp (userid, percentage, topics_completed)
   VALUES (9,'$val', '$val2')
END

Mess around with that, and let me know if that works for you. Also, just out of curiosity, are you able to see which error you're getting?

KnowledgeWoods Consulting
KnowledgeWoods Consulting
5,607 Points

I used the following and it worked perfectely in MS SQL. UPDATE Tablename SET (...) WHERE Column1='SomeValue',Column2='SomeValue' IF @@ROWCOUNT=0 INSERT INTO Tablename VALUES (...)

Thanks for your help....!!!!

Jeremy Menicucci
Jeremy Menicucci
19,227 Points

Oh good deal. Glad you got it worked out!

Jeremy Menicucci
Jeremy Menicucci
19,227 Points
Jeremy Menicucci
Jeremy Menicucci
19,227 Points

ON DUPLICATE KEY UPDATE is your friend in cases like this. I think conditionals largely belong inside functions in MYSQL and I'm not sure writing out the conditional is your best bet here.

I think you may find more luck doing something similar to this, 

$kwsql = "INSERT INTO completion_tracker_pmp(userid, percentage, topics_completed)  VALUES(9,'$val','$val2') ON DUPLICATE KEY UPDATE percentage='$val', topics_completed='$val2'";

$kwresult = $kwdb->query($kwsql);

Let me know if that works out for you. You can also check the mysql docs on this,

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

KnowledgeWoods Consulting
KnowledgeWoods Consulting
5,607 Points

Hi Jeremy, ON DUPLICATE KEY UPDATE works in mysql but not in MS SQL. I need a query for MS SQL