Update with except condition - 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 2005 Forums

SQL Server Administration (2005)

Update with except condition

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 table
set 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,
Sushant
DBA
Virgin Islands(U.K)

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-04 : 11:28:45

update PLU_1
SET 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,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 12:34:17

[code]
update p
SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')
FROM PLU_1 p
where NOT EXISTS (select 1 from PLU_1
Where len(DSPL_DESCR)=40
AND VND_ID = p.VND_ID
AND DSPL_DESCR LIKE '%"%' )
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 1
String 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,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 12:54:28

then it should be this


update p
SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')
FROM PLU_1 p
Where len(p.DSPL_DESCR)=40
AND p.DSPL_DESCR LIKE '%"%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-05 : 12:58:40

Thanks. it worked.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources