Post reply

Update Query

  • February 11, 2010 at 2:40 am

    #216626

    Hi All,

    I'm trying to update a table from another table, I've managed to do this in the past with out problems but struggling today 🙁

    Table1 contains the data I need (NOTES1), an ID and Year. The data need to go into Table2 (NOTES2) against the correct ID and Year. The ID's and Year need to match in both tables.

    I have come up with the following query:

    update TABLE2 set NOTES2 = (select NOTES1 from TABLE1 where YEAR = '2009' and ID in (select ID from TABLE2 where YEAR = '2008')) where YEAR = '2008'

    I have also tried other queries but keep getting the same error:

    Msg 512, Level 16, State 1, Line 1

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    The statement has been terminated.

    Thank you in advanced for any help or Advice 😀

    Scott

  • February 11, 2010 at 2:56 am

    #1117464

    Please check BOL (update statement) and Google before posting your question.

    Try this

    UPDATE t

    SET NOTES2 = s.NOTES1

    FROM TABLE2 t

    JOIN TABLE1 s

    ON t.YEAR = s.YEAR

    AND t.ID = s.ID ;

    On SQL2008 you may want to use the merge statement (check BOL).

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • February 11, 2010 at 3:22 am

    #1117472

    Hi Willem,

    I did have a little scout around the net but kept getting problems 🙁

    Thank you for your suggestion it appears to have worked, but I'm getting an error;

    Msg 8152, Level 16, State 14, Line 1

    String or binary data would be truncated

    I should be able to sort this out though 😀

    Thank you again,

    Scott

  • February 11, 2010 at 3:45 am

    #1117483

    Do NOTES1 and NOTES2 have the same datatype definition and length?

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • February 11, 2010 at 3:52 am

    #1117489

    No - just found out they are completely different :/

    I'm just sorting it out now, but because of the way the software using the database works I got to make sure it changing it doesn't break anything else 🙁

  • February 11, 2010 at 3:57 am

    #1117495

    If you can't change it, you can do an explicit conversion e.g.

    SET NOTES1 = CONVERT(VARCHAR(50),NOTES2)

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • February 11, 2010 at 4:20 am

    #1117505

    Would that be set in the original statement?

    Both are the same datatype but the original NOTEs has a length of 500, the new NOTEs is only 60...

    When you enter data into the software and it goes over the length it normally creates another line in the table and carries on. I might be able to change the length though

    Thanks again for all your help 😀

    Scott

  • February 11, 2010 at 4:28 am

    #1117510

    Yep.

    You can also do this, but be aware that you will lose all text entered after the 60th character.

    UPDATE t

    SET NOTES2 = LEFT(s.NOTES1,60)

    FROM TABLE2 t

    JOIN TABLE1 s

    ON t.YEAR = s.YEAR

    AND t.ID = s.ID ;

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • February 12, 2010 at 5:48 am

    #1117962

    Hi Willem,

    Just to say thank you for all the help, I've managed to complete the transfer - had to do a few more statements on top of this but got there in the end.

    thanks,

    Scott

  • February 12, 2010 at 5:56 am

    #1117965

    Great! - Thank you for the feedback.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

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

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