Update From Multiple Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update From Multiple Tables

Author

Topic

matt.orme
Starting Member

23 Posts

Posted - 2008-03-28 : 13:41:35

I have a mess:

Table1(T1) contains Column1 (C1) which must be appended to Column2 (C2) in Table2 (T2) where an identifying code column(ID) matches in each table. An example of what I want is, where T1.C1 = 'Aluminum' and T2.C2 = 'Material', I would like it to be updated to T2.C2 = 'Material: Aluminum'. I tried this:

UPDATE T2
SET T2.C2 =
(SELECT T1.C1
FROM T1
WHERE T1.ID = T2.ID) + ':' + T2.C2

This did not work out. How should I fix this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-28 : 13:46:07

[code]UPDATE t2
SET t2.C2=t1.C1 + ':'+t2.C2
FROM T2 t2
INNER JOIN T1 t1
ON t1.ID=t2.ID[/code]

matt.orme
Starting Member

23 Posts

Posted - 2008-03-28 : 14:16:26

I get a syntax error when I try that. This is exactly what I typed

UPDATE CooperAttrib
SET CooperAttrib.ATT_1 = CooperAtt_Desc.ATT_1 + ': ' + CooperAttrib.ATT_1
FROM CooperAttrib
INNER JOIN CooperAtt_Desc
ON CooperAtt_Desc.ATT_ID = CooperAttrib.ATT_ID;

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-03-28 : 14:23:21

What is your syntax error saying as when I run you script it runs fine

matt.orme
Starting Member

23 Posts

Posted - 2008-03-28 : 14:28:28

It says Syntax Error (missing operator). Is that the info you need?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-28 : 15:52:35

I dont think error is in your UPDATE. Are you executing this code as part of a batch. If yes,post your full code.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-03-28 : 16:07:22

quote:
Originally posted by matt.orme

It says Syntax Error (missing operator). Is that the info you need?

Matt,

The syntax is fine here.

Are you using MS SQL Server? This is a SQL Server forum; if you're using another RDBMS, you'll need to post in one of those fora.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources