Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to udate column data with history

Proposed Answer How to udate column data with history

  • Thursday, September 20, 2012 3:51 PM
     
     
    Sign In to Vote
    0

    In x table we have y column for y column we are receving below coments as follows.

    Monday - The video request is not complete(then it will updated in y column).

    again for same y column we recevice comments and concatenating as below 

    Tuesday – Video request in progress (this will concanting prevous day comments this day comments it look like this ..(The video request is not complete   Video request in progress)

    Wednesday - Video request in progress.

    In this scenario, only 2 comments will be displayed as the last 2 are repetitive. i.e. The video request is not complete, Video request in progress.......

    Video request in progress   this comments already presents in y column so we don't want this comments....how to comapare data in same columny

    Queries:-


    below query we are concatenating y column which is present in database and xx is which is coming from file to update history daily,
    Update h
          Set Y = IsNull (Y, '') +  XX,
          From #fg r Inner Join x h
          On r.we = h.we

    y is   column for comments already present in database.
    XXit  is daily coming from the file
    we are updating the history

    I am waiting for your respose.

    thanks

    santosh.bangalore

    santoshbangalore

     

All Replies

  • Thursday, September 20, 2012 3:53 PM
    Moderator
     
     
    Sign In to Vote
    0

    Update h
          Set Y = IsNull (Y, '') +  XX,
          From #fg r Inner Join x h
          On r.we = h.we 

        WHERE Y NOT LIKE '%' + XX + '%'

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, September 20, 2012 4:01 PM
     
     
    Sign In to Vote
    0
    suppose Y column contain data like   ''parter space  naomi''   in daily file i have data called naomi then it won't update right.

    santoshbangalore

     
  • Thursday, September 20, 2012 4:08 PM
    Moderator
     
     
    Sign In to Vote
    0
    Yes, correct.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Saturday, September 22, 2012 2:48 AM
     
     
    Sign In to Vote
    1

    Hi Naomi,

    in the y column if we have data called '' santosh  sapace  patil ''  and in next day file we receive as santosh then it won't upated ....so above scenario will fail right.

    very very thanks for your response...i waiting for your response..

    Thanks,

    Santosh Bangalore

    santoshbangalore

     
  • Saturday, September 22, 2012 6:30 AM
     
      Has Code
    Sign In to Vote
    1

    Please try the below tsql - 

    UPDATE h
SET 
  Y = REPLACE(ISNULL(Y,''),XX,'') + ' ' + XX
FROM 
  #fg r 
INNER JOIN x h
  ON r.we = h.we

    I have not rested this, but it should work!

    Hope, this helps!

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

     
  • Saturday, September 22, 2012 7:00 AM
     
     
    Sign In to Vote
    0

    it is not working this is maintaing the history.....please read my question from starting on words..

    thanks,

    santosh

    santoshbangalore

     
  • Saturday, September 22, 2012 9:54 AM
     
     
    Sign In to Vote
    0

    Hi,

    If you have the luxury of using an additional column(say 'Z') to store the last day's string, it is easily doable.

    Just match the incoming string with the column 'Z' and post it in column 'Y'.

    Hope i got your question, as u need it.

    Thanks,

     
  • Sunday, September 23, 2012 1:21 AM
    Moderator
     
     
    Sign In to Vote
    0
    Yes, if you will be using the query I posted, it will not be updated.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Sunday, September 23, 2012 4:03 AM
     
     
    Sign In to Vote
    0

    thanks for your response

    yes correct...in " Y "column i have already " santosh space patil " if next day i got comments like "santosh"  then it should compare last word of data in   " Y '' coloumn  if last word is not equall to" santosh" then it will update h table.....suppose next data contain " patil " then it won't upated.

    Thanks,

    santosh bangalore

    santoshbangalore

     
  • Sunday, September 23, 2012 12:10 PM
     
     
    Sign In to Vote
    0

    Hi Naomi...you have any idea to do above scenario...

    thanks,

    santosh.patil

    santoshbangalore

     
  • Sunday, September 23, 2012 4:25 PM
     
     
    Sign In to Vote
    1

    Santosh,

    Are there clear delimiters that separate each comment in Y? I think you will struggle with string comparisons like these unless you have clear delimiters that make each comment discrete.

    If you were to use a comma to delimit each comment in Y then perhaps a modified version of what Naomi provided would work:

    Update h
          Set Y = IsNull (Y, '') +  XX + ',' ,
          From #fg r Inner Join x h
          On r.we = h.we 

        WHERE ','+ Y NOT LIKE '%,' + XX + ',%'

    I haven't tested this, but I think something along these lines could work

     
  • Sunday, September 23, 2012 9:24 PM
     
     Proposed Answer
    Sign In to Vote
    1

    You should have a separate table for comments with the foreign key pointing to your record.

    It is good practice to have the database in at least 1 normal form...

    http://en.wikipedia.org/wiki/First_normal_form

     
  • Saturday, November 03, 2012 3:17 PM
     
     
    Sign In to Vote
    0
    yes correct....what is the code pls?

    santoshbangalore

     
  • Sunday, November 04, 2012 4:06 PM
     
     
    Sign In to Vote
    1
    You need to redesign your tables, please post first full create scripts for current design and some sample data.
     
  • Tuesday, November 20, 2012 7:52 PM
    Moderator
     
     Proposed Answer Has Code
    Sign In to Vote
    1

    Consider the following table design. You just INSERT a row whenever status is updated.

    CREATE TABLE VideoRequestXref (
	VideoID INT,  -- FK
	CustomerID INT, -- FK
	StatusTimestamp datetime,
	PRIMARY KEY (VideoID, CustomerID, StatusTimestamp),
	StatusCode INT  );  -- FK

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012