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.
Author |
Topic |
muhadmr
Starting Member
3 Posts |
Posted - 2011-05-20 : 09:36:33
|
Hi,I have 2 tables.Table Mid name-- ----1 joe2 aliceTable Cid name payment-- ---- -------1 joe 1001 joe 2002 alice 50I want to change the name column in table M.update M set name='bob' where name='joe'This work fine.How should change the name column in table C?The following does not work. It only update the first row in table C but not the second row.update C set name=M.name from M where C.id=M.idAny idea?I am running SQL Server 2008.Thanksmuhad |
|
Devart
Posting Yak Master
102 Posts |
Posted - 2011-05-20 : 10:07:03
|
For example:UPDATE CSET name=(SELECT name FROM M WHERE id=C.id)ORUPDATE CSET name=M.nameFROM C INNER JOIN M ON C.id=M.idDevart,SQL Server Tools:dbForge Data StudiodbForge Schema ComparedbForge Data ComparedbForge SQL Complete |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-20 : 11:37:25
|
Are you sure that the ID in C is the ID for the name from table M?If so then the name is redundant and it looks like you could have duplicate rows in C i.e. no possible PK.Is ID actually the identifier and not related to the name?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
muhadmr
Starting Member
3 Posts |
Posted - 2011-05-21 : 10:16:43
|
quote: Originally posted by Devart For example:UPDATE CSET name=(SELECT name FROM M WHERE id=C.id)ORUPDATE CSET name=M.nameFROM C INNER JOIN M ON C.id=M.idDevart,SQL Server Tools:dbForge Data StudiodbForge Schema ComparedbForge Data ComparedbForge SQL Complete
Yes. I tried both. But somehow still does not work at my side. The first Select statement above will give "id could not be bound" error.May be its something to do with my table. |
|
|
muhadmr
Starting Member
3 Posts |
Posted - 2011-05-21 : 10:18:38
|
quote: Originally posted by nigelrivett Are you sure that the ID in C is the ID for the name from table M?If so then the name is redundant and it looks like you could have duplicate rows in C i.e. no possible PK.Is ID actually the identifier and not related to the name?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Yes, the Id is actual identifier.Actually the tables are more complicated and they came from legacy system. I simplified the problem here for discussion purpose.Muhad |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2011-05-21 : 14:18:04
|
Based on your example - this worksIF OBJECT_ID('m') > 0 BEGIN DROP TABLE m ENDIF OBJECT_ID('c') > 0 BEGIN DROP TABLE c ENDCreate Table m(ID int,Name Varchar(30))Create Table C(ID int,Name Varchar(30),Payment int)Insert into M Values(1,'joe'),(2,'alice')Insert into C Values(1,'joe',100),(1,'joe',200),(2,'alice',50)Select * from cSelect * from mUpdate MSET name = 'bob'FROM M inner join C on M.ID = C.IDWhere M.Name = 'joe'Update cSET name = 'bob'FROM M inner join C on M.ID = C.ID-- Where c.Name = 'joe' -- optional , If table m has aready been updated prior to running this update, the join condition shoul suffice to taget appropriate recordsSelect * from cSelect * from m |
|
|
|
|
|
|
|