Why cannot update all row? - 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

Why cannot update all row?

Author

Topic

muhadmr
Starting Member

3 Posts

Posted - 2011-05-20 : 09:36:33

Hi,
I have 2 tables.

Table M
id name
-- ----
1 joe
2 alice

Table C
id name payment
-- ---- -------
1 joe 100
1 joe 200
2 alice 50

I 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.id

Any idea?

I am running SQL Server 2008.

Thanks
muhad

Devart
Posting Yak Master

102 Posts

Posted - 2011-05-20 : 10:07:03

For example:

UPDATE C
SET name=(SELECT name FROM M WHERE id=C.id)

OR

UPDATE C
SET name=M.name
FROM C INNER JOIN M ON C.id=M.id

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge 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 C
SET name=(SELECT name FROM M WHERE id=C.id)

OR

UPDATE C
SET name=M.name
FROM C INNER JOIN M ON C.id=M.id

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge 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 works

IF OBJECT_ID('m') > 0 BEGIN DROP TABLE m END
IF OBJECT_ID('c') > 0 BEGIN DROP TABLE c END

Create 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 c
Select * from m

Update M
SET name = 'bob'
FROM M inner join C on M.ID = C.ID
Where M.Name = 'joe'

Update c
SET 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 records

Select * from c
Select * from m

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources