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.
Author |
Topic |
skybvi
Posting Yak Master
193 Posts |
Posted - 2012-01-04 : 10:47:34
|
I want to update a column in a table.I am using:-update tableset column name =....except (sub query giving other COLUMN VALUES as output)I dont want the sub query resulted VALUES in the updated statemnt , How to proceed?Regards,SushantDBAVirgin Islands(U.K) |
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2012-01-04 : 11:28:45
|
update PLU_1SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')where VND_ID NOT IN (select VND_ID from PLU_1 Where len(DSPL_DESCR)=40 AND DSPL_DESCR LIKE '%"%' )VND_ID and DSPL_DESCR are columns.I want all rows of DSPL_DESCR to be updated EXCEPT those which have the above subquery condition.Regards,SushantDBAVirgin Islands(U.K) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-04 : 12:34:17
|
[code]update pSET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')FROM PLU_1 pwhere NOT EXISTS (select 1 from PLU_1Where len(DSPL_DESCR)=40AND VND_ID = p.VND_ID AND DSPL_DESCR LIKE '%"%' )[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2012-01-04 : 12:46:12
|
Hi I am getting this Msg 8152, Level 16, State 14, Line 1String or binary data would be truncated.The statement has been terminated.Actually, Th max value of DSPL_DESCR is 40 and I am replacing "(1 char) by in(2 char)That means any DSPL_DESCR with already 40, wont happen as it will tend to 41 wwich is not allowed.So the whole purpose of me is to update only those rows whcih has DSPL_DESCR of 39 or less. ( thatsy I was doing except for rows with 40 char)Regards,SushantDBAVirgin Islands(U.K) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-04 : 12:54:28
|
then it should be thisupdate pSET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')FROM PLU_1 pWhere len(p.DSPL_DESCR)=40AND p.DSPL_DESCR LIKE '%"%' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2012-01-05 : 12:58:40
|
Thanks. it worked.Regards,SushantDBAVirgin Islands(U.K) |
|
|
|
|
|
|
|