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 2005 Forums
 Transact-SQL (2005)
 Error in update statement

Author  Topic 

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-29 : 12:49:34
Hi,
I am running this :-

UPDATE dbo.ep40100
SET ep40100.eps30ud1 =
case when ((SUBSTRING(ITEMNMBR,1,2)=ep40104.epcategory)
then eps30ud1=dbo.ep40104.epcategory
else ep40100.eps30ud1 =''
end

And getting error -- "Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'then'."

P.S. ep40100 and ep40104 are tables
and eps30ud1, epcategory, and itemnmbr are column names


Regards,
Sushant
DBA
West Indies

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 12:50:31
count your brackets.

==========================================
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.
Go to Top of Page

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-29 : 12:54:04
ohk,
UPDATE dbo.ep40100
SET ep40100.eps30ud1 =
case when ((SUBSTRING(ITEMNMBR,1,2)=ep40104.epcategory))
then eps30ud1=dbo.ep40104.epcategory
else ep40100.eps30ud1 = ''
end

Now i getting :-

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.


Regards,
Sushant
DBA
West Indies
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-06-29 : 14:08:55
Can we see some sample data and expected output. I think, you have two different tables here dbo.ep40100 and dbo.ep40104?

Maybe something like this?
UPDATE A
SET A.eps30ud1 = COALESCE(B.epcategory,'')
FROM ep40100 A
LEFT JOIN ep40104 B ON B.epcategory = SUBSTRING(A.ITEMNMBR,1,2)
Go to Top of Page

skybvi
Posting Yak Master

193 Posts

Posted - 2011-06-29 : 14:48:04
@vijayisonly

That was perfect...

Thanks a lot !!!


Regards,
Sushant
DBA
West Indies
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-06-29 : 15:09:07
Np. Going forward, please provide sample data and expected output, so u can get accurate answers pretty quickly.
Go to Top of Page
   

- Advertisement -