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

SQL Server 2000 Forums

SQL Server Development (2000)

Update Query

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-12-28 : 21:42:16

I have below table and data..

create table #DMT (HSUID varchar(25),
ID int,
Tycd varchar(25)
)

create table #customer (
ID int NOT NULL ,
VS int NOT NULL
)

create table #Customerview (
HSUID varchar(25),
Id int NOT NULL ,
dpe nchar (10) NOT NULL,
FN nchar (3) NOT NULL,
TN nchar (10) NOT NULL,
pv int NOT NULL,
status nchar (2)
)

insert #Customer
select 2, '9' union all
select 3, '1' union all
select 4, '1'

insert #Customerview
select 'VT-4',4,'002', 'VS', 'psn', '2','Y' union all
select 'VT-4',2,'001', 'VS', 'psn', '9','N'

INSERT #DMT
Select 'VT-4',3,'000' union all
select 'VT-4',2,'001' union all
select 'VT-4',4,'002'

I want a update query which will update the Field value of'VS' of #customer table with #customerview table PV value '2' with satus 'Y'.

Rule:
Need to identify ID by the min (typcd) of #dmt table and find the column which needs to be updated in the #customer table
wth the results of (#dmt table) id or find the id which exists in the #customer table.and update the customer table Field value of'VS' with #customerview table PV value '2' with status 'Y'.
results should like:

#Customer table

ID VS
--- --
2 9
3 2
4 1

swatib
Posting Yak Master

173 Posts

Posted - 2006-12-29 : 01:39:40

quote:
Originally posted by sqldba2k6
Rule:
update the customer table Field value of'VS' with #customerview table PV value '2' with status 'Y'.

HI,

Please tell me how you are replacing a value with two values?
Please clarify so that i can try for the solution.

Njoy Life

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-12-30 : 01:49:55

Hi sqldba2k6,
For me atleast I am getting the result you expect if I run a query like this.

declare @tycd int
declare @id int
select @tycd=min(tycd) from #dmt
select @id=id from #dmt where tycd=@tycd

update #Customer set #Customer.vs= #Customerview.pv from #Customerview where #Customerview.status='Y'and #Customer.id =@id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources