Post reply

Basic SQL query for update

  • May 7, 2009 at 5:02 am

    #103732

    hi ,

    i have two tables , Tags and Customer_Tags

    Tags

    ---------------------------------------------------

    TagID (int AutoNumber PK) , TagName (varchar (50)

    ---------------------------------------------------

    Customer_Tags

    ------------------------------------------------------------------------

    CustomerID (int AutoNumber FK), TagId (int AutoNumber FK), Count (int)

    ------------------------------------------------------------------------

    i want to create an SP "Increase_Count" that will take 2 parameters CustomerID and TagName and Increment the Count by 1.

    can anybody please write the StoreProcedure as i am new to SQL.

    Thanks

  • May 7, 2009 at 5:25 am

    #990241

    yasser.azeem (5/7/2009)

    hi ,

    i have two tables , Tags and Customer_Tags

    Tags

    ---------------------------------------------------

    TagID (int AutoNumber PK) , TagName (varchar (50)

    ---------------------------------------------------

    Customer_Tags

    ------------------------------------------------------------------------

    CustomerID (int AutoNumber FK), TagId (int AutoNumber FK), Count (int)

    ------------------------------------------------------------------------

    i want to create an SP "Increase_Count" that will take 2 parameters CustomerID and TagName and Increment the Count by 1.

    can anybody please write the StoreProcedure as i am new to SQL.

    Thanks

    what have you tried so far? we like to help those who help themselves, so that you get something out of the information as well, and with first time posters we worry that homework questions are being asked, so we hesitate ato throw an answer out right away.

    we don't end up doing someones homework on their behalf.

    We can help with the logic right away, however. Your customer tags should have just two columns in my opinion...CustomerID and the TagID...the count should Always be calculated in a view....SELECT CustomerID,TagID,Count(*) As TheCount Group By CustomerID,TagID.

    so the table might have three rows...custmerID=1, with TagId =1,2 and 3. the view calculates the needed values automatically.

    with that structure, you can have your proc simply insert into the table in question, and not try to update a bunch of rows with the "count" when it shouldn't need to.

    that way it is calculated on demand, when it is needed, instead of updating a static table.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 7, 2009 at 10:07 am

    #990471

    Lowell (5/7/2009)

    We can help with the logic right away, however. Your customer tags should have just two columns in my opinion...CustomerID and the TagID...the count should Always be calculated in a view....SELECT CustomerID,TagID,Count(*) As TheCount Group By CustomerID,TagID.

    Hmmm. I usually agree with you Lowell, but I have to admit that I do not understand this. Your suggestion would mean that we would intentionally be creating duplicate records in CustomerTags and would never be able to have a primary key on that table. That seems very non-relational. And in exchange, all we get out of it is the ability to never have to do updates: just inserts and deletes instead.

    Unless I am missing something, that doesn't seem right.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 7, 2009 at 10:17 am

    #990487

    Customer_Tags

    ------------------------------------------------------------------------

    CustomerID (int AutoNumber FK), TagId (int AutoNumber FK), Count (int)

    ------------------------------------------------------------------------

    i read his requirement as being that the Customer_Tags table was a many-to-many table with CustomerId , with FK's to other tables....

    As i read it, i thought if he had this data in the table:

    CustomerID TagID TheCount

    ---------- ----- --------

    7 1 1

    I thought the requirment was if he adds a new row for that specific customer with TagId = 2, he wanted the count() for ALL Rows for Customer 7 to have a count of 2, since there is now two tagID's.

    CustomerID TagID TheCount

    ---------- ----- --------

    7 1 2

    7 2 2

    I didn't see any value trying to maintain the count in a table itself.

    Maybe i read the requirement too fast, is that what you see as well?

    --edit--

    looking at it, the potential view I threw out there is screwed up for sure...

    something more like

    SELECT CustomerId,TagId,X.TheCount

    FROM Customer_Tags

    INNER JOIN (SELECT CustomerId,Count(*) AS TheCount FROM Customer_Tags GROUP BY CustomerId) X

    ON Customer_Tags.CustomerId= X.CustomerId

    is what i thought it should be.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 7, 2009 at 10:50 am

    #990517

    The way that I read it was just, having a record like this:

    CustomerID TagID TheCount

    ---------- ----- --------

    7 1 1

    that he wanted to update the count, like this:

    CustomerID TagID TheCount

    ---------- ----- --------

    7 1 2

    Implying that TheCount was just a count of duplicate applications of the same tag to the same customer.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 7, 2009 at 10:55 am

    #990521

    yasser.azeem (5/7/2009)

    hi ,

    i have two tables , Tags and Customer_Tags

    Tags

    ---------------------------------------------------

    TagID (int AutoNumber PK) , TagName (varchar (50)

    ---------------------------------------------------

    Customer_Tags

    ------------------------------------------------------------------------

    CustomerID (int AutoNumber FK), TagId (int AutoNumber FK), Count (int)

    ------------------------------------------------------------------------

    i want to create an SP "Increase_Count" that will take 2 parameters CustomerID and TagName and Increment the Count by 1.

    can anybody please write the StoreProcedure as i am new to SQL.

    Thanks

    FYI: to the OP, assuming that I understand your request correctly, here is how I would do it:

    CREATE Proc CustomerTags_IncrementCount(

    @CustomerID int,

    @TagName varchar(50)

    ) AS

    UPDATE CustomerTags

    Set [Count] = [Count]+1

    Where CustomerID = @CustomerID

    And TagID = (Select t.TagID

    From Tags t

    Where TagName = @TagName)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 7, 2009 at 10:57 am

    #990524

    Though Lowell is right in that my simple solution does not account for the possibility that the CustomerTag record may not exist yet (ie. Count = 0) and may need to be created.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 7, 2009 at 11:12 pm

    #990848

    hi all,

    Thanks for your replies

    yes, i did my homework , the problem is that it is my first time that i have to create an SP. 🙂

    so i want to make sure that this is efficient.

    i used following SQL statment.

    Update Customer_Tags

    Set Customer_Tags.[Count] = Customer_Tags.[Count] + 1

    from Customer_Tags join Tags

    on Tags.TagID = Customer_Tags.TagID

    where Customer_Tags.CustomerID = @CustomerID

    and Tags.TagName = @TagName

    its working fine just to make sure that my SQL statement is not slow, i mean to say is there any fast way to achieve this.

    Thanks all.

  • May 7, 2009 at 11:33 pm

    #990852

    Looks OK to me.

    Also, this is pretty straight-forward, I doubt that there is anyway to speed this up.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy