Update table from another table

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

Transact-SQL (2000)

Update table from another table

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2008-03-26 : 13:45:35

I am having some trouble here. I am trying to update some data based off of another tables data but I am getting a syntax error near CASE. Any help would be great

update member set member.firstname= ttupdates.firstname, member.lastname = ttupdates.lastname, member.spouse=ttupdates.spouse,
member.spouselast = ttupdates.spouselast,
case
when member.address1valid=1 then member.address2 = ttupdates.address1, member.city2=ttupdates.city1,
member.state2 = ttupdates.state, member.zip2 = ttupdates.zip
else member.address1 = ttupdates.address1, member.city1= ttupdates.city1,
member.state1 = ttupdates.state, member.zip1=ttupdates.zip end,
case
when member.phone1prime=1 and member.contactstatus<>'Wrong Phone Num' then
member.phone2client = ttupdates.home_phone
else member.phone1client=ttupdates.home_phone, member.phone1prime=1 end,
member.phonealt = ttupdates.work_phone
from member inner join ttupdates on
member.primaryid = ttupdates.contract_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-26 : 14:03:53

You cant use case like this. Change like this:-

update member set member.firstname= ttupdates.firstname, member.lastname = ttupdates.lastname, member.spouse=ttupdates.spouse,
member.spouselast = ttupdates.spouselast,
member.address2 = case
when member.address1valid=1 then ttupdates.address1 end, member.city2=case
when member.address1valid=1 then ttupdates.city1 end,
member.state2 =case
when member.address1valid=1 then ttupdates.state end,
member.zip2 =case
when member.address1valid=1 then ttupdates.zip end
member.address1 =case
when member.address1valid<>1 then ttupdates.address1 end, member.city1=case
when member.address1valid<>1 then ttupdates.city1 end,
member.state1 =case
when member.address1valid<>1 then ttupdates.state end, member.zip1=case
when member.address1valid<>1 then ttupdates.zip end,
member.phone2client =case
when member.phone1prime=1 and member.contactstatus<>'Wrong Phone Num' then
ttupdates.home_phone end,
member.phone1client=case
when member.phone1prime<>1 and member.contactstatus='Wrong Phone Num' then
ttupdates.home_phone end, member.phone1prime=case
when member.phone1prime<>1 and member.contactstatus='Wrong Phone Num' then
1 end,
member.phonealt = ttupdates.work_phone
from member inner join ttupdates on
member.primaryid = ttupdates.contract_id

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 14:06:07

case returns a value it's not a control of flow atatement.

member.address2 = case when member.address1valid=1 then ttupdates.address1 else member.address2 end ,

That's is you want to leave the value untouched if not updated - you might want a null.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

blackX
Posting Yak Master

102 Posts

Posted - 2008-03-26 : 14:15:37

thanks but Your solution dont work. I am getting an error saying that string or binary data would be truncated.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 14:32:48

You need to check the updated values against the column lengths.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

blackX
Posting Yak Master

102 Posts

Posted - 2008-03-26 : 14:34:49

quote:
Originally posted by nr

You need to check the updated values against the column lengths.

thanks but that is not the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 14:40:42

Try replacing all your case statments with the syntax I posted (decide whether you want null or no update) then comment out most of the updates bar one then run it. If that works uncomment updates untill you find the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

blackX
Posting Yak Master

102 Posts

Posted - 2008-03-26 : 14:45:22

Our member table houses two address. If the first one is marked valid then the new address info should go into the second address, otherwise it should go to address1. Same logic for the phone numbers, if phone1 is prime then new phone goes into phone2. Does this make sense?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 14:53:20

Then I'm guessing
member.address1 = case when member.address1valid<>1 then ttupdates.address1 end ,
member.address2 = case when member.address1valid=1 then ttupdates.address1 end

That'll leave addr1 in addr2 or addr1 and the other as null. Assuming that valid can't be null.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 16:00:10

Please post your table structure if you need more help on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources