Update Join - 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 Join

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2011-09-06 : 22:44:12

Hi Guys,

The following select statement returns 68000 odd rows.

select * from ts_customer_marketing_data
right join ts_customer
on ts_customer_marketing_data.customermarketingdata_customer_id = ts_customer.customer_id
where ts_customer.customer_reference_code in
(select patronNumber from NCBData_July05.dbo.final_load01)

I wanted to update a column called "customermarketingdata_data15" in "ts_customer_marketing_data" table on the above data set.

But when I do the following update only 12241 update is happening.
Something is wrong with the update. The data set is not same as the above even though the join is same.

what am I doing wrong?

update ts_customer_marketing_data
set ts_customer_marketing_data.customermarketingdata_data15 = 'YES'
from ts_customer_marketing_data
right join ts_customer
on ts_customer_marketing_data.customermarketingdata_customer_id = ts_customer.customer_id
where ts_customer.customer_reference_code in
(select patronNumber from NCBData_July05.dbo.final_load01)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 22:51:09

if you want to update ts_customer_marketing_data there's no point in taking right join as it will only update not null values in table which will make it reduce to inner join. so record count you might be seeing will be one corresponding to below query


select * from ts_customer_marketing_data
inner join ts_customer
on ts_customer_marketing_data.customermarketingdata_customer_id = ts_customer.customer_id
where ts_customer.customer_reference_code in
(select patronNumber from NCBData_July05.dbo.final_load01)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2011-09-06 : 23:13:16

Thanks Vishakh.

Then How can I update the right join result set of 68000 odd records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 23:20:34

what? unless you've match in your ts_customer_marketing_data how will you update it?
so question itself is meaningless
please keep in mind that 68000 is not actual records in your table which are to be updated its just total number of rows returned from ts_customer as per condition written( because of right join).
you've only 12241 of them in your table ts_customer_marketing_data and that what it has updated already.
i suggested you read about right join and understand how it work from books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2011-09-07 : 10:49:32

Hi Visakh,

I get it now. Thanks a lot for this.

Regards,
Shiyam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:40:05

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources