update multiple columns in an update statement

debbiecoates · August 15th, 2008, 06:15 AM

Can anyone help me,

i have two rows in a table, say row a and row b, i want to be able to update row a from row b

ie

Update Board
set BankAddress = (Select BankAddress from Board where projectid = 29),
BankBuildingSocietyName = (Select BankBuildingSocietyName from Board where projectid = 29)
where projectID = 10004

row a has id of 10004 and row b has an id of 29

How i have been doing it is to select each field in in turn (in brackets)

however, i think i can do it in one, but not sure of the syntax, I have tried this

update Board A
set A.BankAddress, A.BankBuildingSocietyName = (select B.BankAddress, B.BankBuildingSocietyName from Board B where B.ProjectID = 29)
where A.Projectid = '10004';

but get this error

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'A'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'where'.

and i cant work out how

debbie

Peso · August 17th, 2008, 04:01 AM

Code:

UPDATE        b
SET        b.BankAddress = x.BankAddress
        b.BankBuildingSocietyName = x.BankBuildingSocietyName
FROM        Board AS b
INNER JOIN    Board AS x ON x.ProjectID = 29
WHERE        p.ProjectID = 10004