Update Query

Question

Post reply

Update Query

scott.lucas-1068252

SSC Enthusiast

Points: 116
More actions
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

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

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 1

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]

scott.lucas-1068252 SSC Enthusiast Points: 116 More actions · Answer 2

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 3

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

Regards,

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

scott.lucas-1068252 SSC Enthusiast Points: 116 More actions · Answer 4

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 🙁

wschampheleer SSCertifiable Points: 5504 More actions · Answer 5

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]

scott.lucas-1068252 SSC Enthusiast Points: 116 More actions · Answer 6

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 7

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]

scott.lucas-1068252 SSC Enthusiast Points: 116 More actions · Answer 8

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 9

Great! - Thank you for the feedback.

Regards,

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