January 4, 2012 at 8:46 am
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,
SKYBVI
Regards
Sushant Kumar
MCTS,MCP
January 4, 2012 at 9:06 am
SKYBVI (1/4/2012)
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,
SKYBVI
Something like this?
update t1
set col1 = ???
from table t1 left join (subquery) t2 on t1.??? = t2.???
where t2.id is null
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 4, 2012 at 9:28 am
Hi,
I am not joining any table , its just a subquery..
Hope my query will clarify you more:--
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,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
January 4, 2012 at 11:15 am
where VND_ID NOT IN (select VND_ID from PLU_1
Where len(DSPL_DESCR)=40
AND DSPL_DESCR LIKE '%"%' )
This is functionally equivalent to Phil's LEFT JOIN.
You could also use NOT EXISTS as a correlated subquery.
Your problem is unclear - is your statement not working, or is it slow?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 4, 2012 at 11:20 am
I must also be missing something. What's wrong with the solution you already posted?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2012 at 11:47 am
ChrisM@home (1/4/2012)
where VND_ID NOT IN (select VND_ID from PLU_1
Where len(DSPL_DESCR)=40
AND DSPL_DESCR LIKE '%"%' )
This is functionally equivalent to Phil's LEFT JOIN.
You could also use NOT EXISTS as a correlated subquery.
Your problem is unclear - is your statement not working, or is it slow?
I getting this error
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Regards.
Skybvi
Regards
Sushant Kumar
MCTS,MCP
January 4, 2012 at 11:52 am
Probably something in the UPDATE part of your statement. How about posting the whole statement?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 4, 2012 at 12:26 pm
The problem is that your REPLACE function is increasing the length of the string beyond the maximum allowed and you haven't correctly accounted for that fact. Given your subquery, I assume that the max length of that field is 40. The subquery will only account for fields that have one instance of the replaced string. Try the following instead.
update PLU_1
SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')
where VND_ID NOT IN (select VND_ID from PLU_1
Where len(replace ([DSPL_DESCR],'"','in'))>=40
AND DSPL_DESCR LIKE '%"%' )
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2012 at 12:32 pm
Actually, why are you using a subquery in the first place. Doesn't the following give you what you want?
UPDATE PLU_1
SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')
WHERE AND DSPL_DESCR LIKE '%"%'
AND len(replace ([DSPL_DESCR],'"','in'))<40
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2012 at 12:33 pm
drew.allen (1/4/2012)
The problem is that your REPLACE function is increasing the length of the string beyond the maximum allowed and you haven't correctly accounted for that fact. Given your subquery, I assume that the max length of that field is 40. The subquery will only account for fields that have one instance of the replaced string. Try the following instead.
update PLU_1
SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')
where VND_ID NOT IN (select VND_ID from PLU_1
Where len(replace ([DSPL_DESCR],'"','in'))>=40
AND DSPL_DESCR LIKE '%"%' )
Drew
That looks perfect , only change i would do in your syntax is <=40
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
January 4, 2012 at 12:35 pm
drew.allen (1/4/2012)
Actually, why are you using a subquery in the first place. Doesn't the following give you what you want?
UPDATE PLU_1
SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')
WHERE AND DSPL_DESCR LIKE '%"%'
AND len(replace ([DSPL_DESCR],'"','in'))<40
Drew
Yup,
This fetches correct
Update PLU_1
SET DSPL_DESCR = REPLACE(PLU_1.[DSPL_DESCR], '"', 'in')
WHERE LEN(REPLACE(PLU_1.[DSPL_DESCR], '"', 'in') ) <= 40
AND PLU_1.DSPL_DESCR LIKE '%"%'
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
January 4, 2012 at 12:37 pm
Using <= in that one will be the opposite of what you want. Use Drew's last example. No reason to overcomplicate this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2012 at 1:30 pm
drew.allen (1/4/2012)
Actually, why are you using a subquery in the first place. Doesn't the following give you what you want?
UPDATE PLU_1
SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')
WHERE AND DSPL_DESCR LIKE '%"%'
AND len(replace ([DSPL_DESCR],'"','in'))<40
Drew
drew.allen (1/4/2012)
The problem is that your REPLACE function is increasing the length of the string beyond the maximum allowed and you haven't correctly accounted for that fact. Given your subquery, I assume that the max length of that field is 40. The subquery will only account for fields that have one instance of the replaced string. Try the following instead.
update PLU_1
SET DSPL_DESCR = replace ([DSPL_DESCR],'"','in')
where VND_ID NOT IN (select VND_ID from PLU_1
Where len(replace ([DSPL_DESCR],'"','in'))>=40
AND DSPL_DESCR LIKE '%"%' )
Drew
Drew - say there are ten rows with the same VND_ID and only one of them matches the filter?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 4, 2012 at 2:56 pm
ChrisM@home (1/4/2012)
say there are ten rows with the same VND_ID and only one of them matches the filter?
You'll need to post sample data illustrating the problem and the desired results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 5, 2012 at 5:43 am
Thanks everyone.
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply