Whats wrong with my update statement?
-
I am trying to formulate some T-SQL to update a new column in a sub-customer table (cCustCode) with the value of cCustCode from its parent table. The parent table is called Customer and the child table is called SubCust. They are related at the moment using CustomerKey. I have tried the following SET statement but it only updates with a single value. What am I doing wrong
update subcust
set ccustcode = customer.ccustcode
from subcust join customer
on subcust.customerkey = customer.customerkey
I have also tried the following statement but it still only sets ccustcode to a single value :-
update subcust
set subcust.ccustcode =
(select ccustcode from customer
where subcust.customerkey = customer.customerkey)
Any ideas much appreciated.
-
It seems okay - is there a unique custcode for each customerkey?
create table customer
(
customerkey int,
ccustcode varchar(3)
)
go
create table subcust
(
customerkey int,
custcode varchar(3)
)
go
insert into customer values(1, 'A')
go
insert into customer values(2, 'B')
go
insert into customer values(3, 'C')
go
insert into subcust values(1, null)
go
insert into subcust values(2, null)
go
insert into subcust values(3, null)
go
select * from customer
go
customerkey ccustcode
----------- ---------
1 A
2 B
3 C
select * from subcust
go
customerkey custcode
----------- --------
1 NULL
2 NULL
3 NULL
update subcust
set custcode = customer.ccustcode
from subcust join customer
on subcust.customerkey = customer.customerkey
go
select * from subcust
go
customerkey custcode
----------- --------
1 A
2 B
3 C
[font="Courier New"]ZenDada[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply