Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Unanswered: Replace data in SQL server table column

    What is the correct syntax to replace a field data nvarchar(50)

    Current data = 0020-10-02
    Change = 2003-10-02

    Thank you in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    West
    Posts
    101

    Re: Replace data in SQL server table column

    Originally posted by josephjthomas
    What is the correct syntax to replace a field data nvarchar(50)

    Current data = 0020-10-02
    Change = 2003-10-02

    Thank you in advance.
    Update table_name
    set (column_name='2003-10-02)
    where (select * from table_name where column_name='0020-10-02')

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why do you assume it's 2003?

    DECLARE @x varchar(50), @y varchar(50)
    SELECT @x = '0020-10-02'

    SELECT SUBSTRING(@x,3,2)+'03'+RIGHT(@x,6)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2004
    Posts
    31
    It's definitely 2003. The entry was mis-typed.

    I tried the Update query but that didn't work. Even adding the missing hypen.

    Brett,

    If I try what you posted, how does it process the update?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(x varchar(50))
    GO
    
    INSERT INTO myTable99(x)
    SELECT '0020-10-02' UNION ALL
    SELECT '2004-10-02'
    GO
    
    SELECT * FROM myTable99
    
    UPDATE myTable99
       SET x = SUBSTRING(x,3,2)+'03'+RIGHT(x,6)  
     WHERE x = '0020-10-02'
    
    SELECT * FROM myTable99
    GO
    
    
    DROP TABLE myTable99
    GO

    Or can just had code it in the set
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    Originally posted by josephjthomas
    It's definitely 2003. The entry was mis-typed.

    I tried the Update query but that didn't work. Even adding the missing hypen.

    Brett,

    If I try what you posted, how does it process the update?
    My bad:

    UPDATE table_name
    SET column_name = '2003-10-02'
    WHERE EXISTS
    (SELECT *
    FROM table_name
    WHERE column_name = '0020-10-02')

  7. #7
    Join Date
    Apr 2004
    Posts
    31
    That worked great! Thank you!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Ida Hoe
    My bad:

    UPDATE table_name
    SET column_name = '2003-10-02'
    WHERE EXISTS
    (SELECT *
    FROM table_name
    WHERE column_name = '0020-10-02')
    Where's the coorelation...

    Why not a simple WHERE?

    That'll update all rows...I was suprised it ran...

    Code:
    
    USE Northwind
    GO
    
    CREATE TABLE myTable99(x varchar(50))
    GO
    
    INSERT INTO myTable99(x)
    SELECT '0020-10-02' UNION ALL
    SELECT '2004-10-02'
    GO
    
    SELECT * FROM myTable99
    
    UPDATE  myTable99
       SET  x = '2003-10-02'
     WHERE  EXISTS ( SELECT *
    		   FROM myTable99
    		  WHERE x = '0020-10-02')
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2004
    Posts
    31
    Sorry. I meant to say Brett's script ran and not Ida Hoe.

    Thank you both. :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •