Update query based on subquery returning many row

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)

Update query based on subquery returning many row

Author

Topic

steppinthrax
Starting Member

27 Posts

Posted - 2009-07-01 : 16:09:45

This is a tough one and one I've been working on most of the day. I have a table that has a field where values are seperated by commas. For example a particular field has values looking like this.

01,88,856,3335,32737843505,Water
01,45,663,9658,68709793012,Water
01,22,123,2678,32344035894,Water
01,99,433,2158,09760154123,Water

I want to simply update the data after the last comma. I tried first using and update query with a subquery representing the Left(27,field) + @new_value, but that dosen't work because it returns more then one value. I tried temp tables and cursors. All do the job of replacing the last value. However, they all modify the first 27 characters WHICH MUST STAY THE SAME. I generate a temp table and try to update the temp table against the fields however, it duplicates the first 27 characters onto every record in that field. The temp table is correct (first 27 char untouched). However the outcomming data looks like this. (using the first record for every single record).

01,88,856,3335,32737843505,New_value
01,88,856,3335,32737843505,New_value
01,88,856,3335,32737843505,New_value
01,88,856,3335,32737843505,New_value

BTW: This is an application specific table so I can't modify the table to seperate these values.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 16:14:37

What are the conditions to update special records and not all records?

No, you're never too old to Yak'n'Roll if you're too young to die.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 16:18:49

see if you can make sense of this


DECLARE @MYVAR VARCHAR(MAX)
DECLARE @NEWTXT VARCHAR(MAX)

SET @MYVAR = '01,88,856,3335,32737843505,Water'
SET @NEWTXT = 'New_value'

SELECT /*REVERSE(@MYVAR), CHARINDEX(',', REVERSE(@MYVAR)), SUBSTRING(REVERSE(@MYVAR), CHARINDEX(',', REVERSE(@MYVAR)), LEN(@MYVAR)),*/ REVERSE(SUBSTRING(REVERSE(@MYVAR), CHARINDEX(',', REVERSE(@MYVAR)), LEN(@MYVAR))) + @NEWTXT

steppinthrax
Starting Member

27 Posts

Posted - 2009-07-01 : 16:21:34

quote:
Originally posted by webfred

What are the conditions to update special records and not all records?

No, you're never too old to Yak'n'Roll if you're too young to die.

I can apply connections to select only those records that I've listed, within the where clause of the query.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 16:26:43

did you try that select statement above?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-01 : 16:31:22

quote:
Originally posted by steppinthrax

quote:
Originally posted by webfred

What are the conditions to update special records and not all records?

No, you're never too old to Yak'n'Roll if you're too young to die.

I can apply connections to select only those records that I've listed, within the where clause of the query.

In that case your initial update query should work fine.

update t
set <column> = left(<column>,27) + 'New_Value'
from <table> t
where <ur condition>

If this doesn't work, pleas epost back with the query you tried

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 16:35:13

declare @yak_table table (yak_column varchar(255))
insert @yak_table
select '01,88,856,3335,32737843505,Water' union all
select '01,45,663,9658,68709793012,Water' union all
select '01,22,123,2678,32344035894,Water' union all
select '01,99,433,2158,09760154123,Water'

select * from @yak_table

update @yak_table
set yak_column=left(yak_column,27)+'NoWater'
where yak_column = '01,45,663,9658,68709793012,Water'

select * from @yak_table

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 17:02:30

You need to normalize your data

Storing data in a database in this manner has little value and is a pain to maintain.

You should saving rows with a key and it's attributes

Post your table DDL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 17:04:28

Hi Brett!
OP posted: BTW: This is an application specific table so I can't modify the table to seperate these values.

No, you're never too old to Yak'n'Roll if you're too young to die.

steppinthrax
Starting Member

27 Posts

Posted - 2009-07-04 : 20:48:04

quote:
Originally posted by webfred

Hi Brett!
OP posted: BTW: This is an application specific table so I can't modify the table to seperate these values.

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes this is correct, thanks webfed.

This is an application specific database and i can't make modifications to the schema or way the data is stored.

steppinthrax
Starting Member

27 Posts

Posted - 2009-07-04 : 21:04:19

Got it guys

update table
set col1= left(col1,27) + 'bob'
where col2 = 'abc' AND col3 = 'def'

It's actually quite easy. I was doing a subquery, thinking when I was doing an update it would not rembmer itself. It worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources