Function Query

Question

Function Query

b_boy

SSCertifiable

Points: 5145
More actions
April 23, 2009 at 10:00 am

#73607

I have scenario to update a col A, with col B.
If col A has a record like '%A' and col B is blank, then update col B with col A and delete the record in col A.
I would even like to place the query a function....can anyone help?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

b_boy (4/23/2009)
I have scenario to update a col A, with col B.
If col A has a record like '%A' and col B is blank, then update col B with col A and delete the record in col A.
I would even like to place the query a function....can anyone help?

How about a simple update, not in a function? (Untested code follows)

update TABLEA

set colB = colA,

colA = ''

where colA like '%A'

and (colB is null or colB = '')

If this doesn't suffice, please read the link in my signature and post relevant DDL/DML code so that we can assist you better.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ramesh Swaminathan Newbie Points: 1 More actions · Answer 2

Hope the following code will address your requirement if your table has a primary key.

DECLARE @Pkey int

SELECT @PKey = Table_Primary_Key FROM Your_Table_Name

WHERE COLA LIKE '%A' AND COLB IS NULL

UPDATE Your_Table_Name SET COLB = COLA,

COLA = NULL

WHERE Table_Primary_Key = @PKey

Table_Primary_Key - replace with primary column of your table.

Your_Table_Name - replace with your table name.

b_boy SSCertifiable Points: 5145 More actions · Answer 3

@ramesh-2 Swaminathan: couldnt place the query within a function because of the update keyword...any ideas?

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

You can't modify data in base tables from a user defined function.

If you want to reuse this TSQL you could consider putting it in a stored procedure.