check for existing and add 1 - 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

General SQL Server Forums

New to SQL Server Programming

check for existing and add 1

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-02 : 18:31:10

Hi All,

I have a table with 3 columns (my_table)
col1 = id )1,2, 3 ....)
col2 = action (fail or pass) only
col3 = NewID

now I have to populate NewID if I have col2 = pass.
Also before generating a newID, I have to llok for that in Other_table
find its maximum value and add1 if it exist in other_table.
if not, then it should generate from 100.
newID specifications
1. it must have a prefix 'AA3'
2. it has 15 digits total

My code for scenario when it does not exist in other_table: (not generating 15 digits newID)

UPDATE my_table
SET newID = new

from
(select newID,
New = CASE action
WHEN 'Pass'

THEN 'AA3'
+ RIGHT (''+ convert (varchar(15), row_number() over (partition by action order by action)
+ 99), 15)
end
end

from my_table)
my_table

Please correct..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 20:23:58

whats the field in other table you have to use for looking up of newid value? also what are columns by which you need to do the lookup?

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

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-02 : 21:45:07

oldID from other_table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 21:54:10

UPDATE t
SET newID = new

from
(select newID,
New = 'AA3'
+ RIGHT (''+ COALESCE(Prev+1,convert (varchar(15), row_number() over (partition by action order by action)
+ 99)), 15)

from my_table m
left join (select max(oldID) as Prev from other_table) o
ON 1=1

WHERE m.action='PASS'
)t
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources