SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


update columns dynamically


update columns dynamically

Author
Message
mxy
mxy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1530 Visits: 3972
i am trying to setup a process to update table and associated columns specified in admin tables.

admin table have columns tb_name and column_name, flag

ex one table may have one column other table may have 6 columns that need to updated. Base on flag column y/n i am going to update those columns.

Can any one help me out how to write a dynamic update statement in this case ?

thanks for help!!!
Bhuvnesh
Bhuvnesh
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23960 Visits: 4079
Definitely you wil get help here some stuf is still missing , you havent mentieond that on what basis you will do the update .

and how would you select that which column and which tables need to be picked up ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
mxy
mxy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1530 Visits: 3972
whatever specified in the adim table like customer and columns fname and lname status as Y

i want to build a update statement whatever specified in admin table
Sean Lange
Sean Lange
SSC Guru
SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)

Group: General Forum Members
Points: 112362 Visits: 18278
On these forums you will find lots and lots of people willing and able to help. However, in order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mxy
mxy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1530 Visits: 3972
i am new bee let me try to provide actual requirement

Create table dbo.UpdateDef( SourceTableName VARCHAR(100),ColumnName VARCHAR(100),Status_fl CHAR(1))
go
insert into dbo.UpdateDef values ( 'dbo.Src','NAME','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src','ADDRESS','N')
go
insert into dbo.UpdateDef values ( 'dbo.Src','AGE','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src1','FName','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src2','LName','N')
go
insert into dbo.UpdateDef values ( 'dbo.Src2','SSN','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src','phone','Y')
go

i want to generate update statements as below based on information available in update def table

update Src a
set a.name = replace(a.name,'s','t')
,a.age = replace(a.name,'2','3')
Sean Lange
Sean Lange
SSC Guru
SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)

Group: General Forum Members
Points: 112362 Visits: 18278
mxy (10/23/2013)
i am new bee let me try to provide actual requirement

Create table dbo.UpdateDef( SourceTableName VARCHAR(100),ColumnName VARCHAR(100),Status_fl CHAR(1))
go
insert into dbo.UpdateDef values ( 'dbo.Src','NAME','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src','ADDRESS','N')
go
insert into dbo.UpdateDef values ( 'dbo.Src','AGE','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src1','FName','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src2','LName','N')
go
insert into dbo.UpdateDef values ( 'dbo.Src2','SSN','Y')
go
insert into dbo.UpdateDef values ( 'dbo.Src','phone','Y')
go

i want to generate update statements as below based on information available in update def table

update Src a
set a.name = replace(a.name,'s','t')
,a.age = replace(a.name,'2','3')




HUH??? Where do the values inside the replace come from? From what I see you are going to have to use dynamic sql for this. You will probably need to provide ddl for Src as well. Most importantly is you need to provide some details about what it is you are trying to do. Keep in mind that we know only the information you have posted.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mxy
mxy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1530 Visits: 3972
thanks for your help
here is the structure of the src tables and other

Create table dbo.src
(
ID int Identity(1,1) PRIMARY KEY CLUSTERED
,Name varchar(100)
,Address varchar(255)
,Phone varchar(25)
,Age INT
)
insert into src values ('sam','main street pa', '123-456-789', 30)
insert into src values ('tim','state street pa', '134-456-789', 20)

Create table dbo.src1
(
ID_scr1 int Identity(1,1) PRIMARY KEY CLUSTERED
,FNAME varchar(100)
,LNAME varchar(255)
)
insert into src1 values ('sam','john')
insert into src1 values ('jim','tim')
insert into src1 values ('kim','tom')

CREATE TABLE dbo.scr2
(
phone varchar(30)
)

insert into scr2 values(234-456-7899)
insert into scr2 values(222-436-7339)

These tables don't have any relationship. the reason i am creating a admin table(updatedef) is to store which columns i need to update. tomorrow i may need to add new column or remove a column. For this i have included a flag Y or N so that i can generate update statement based on Status_fl column. i want to update string columns (name, address , country), is to REPLACE S with t and number columns (age, phone) is to replace 2 with 3.
let me know if you need more info
Sean Lange
Sean Lange
SSC Guru
SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)

Group: General Forum Members
Points: 112362 Visits: 18278
What is the purpose of this? This seems like a very rudimentary method for scrambling your data slightly. Maybe if you could explain what the ultimate goal is we can help you find a far less convoluted way of doing it.

Given what you have posted you will have to examine sys.columns to determine the datatype of each update. This is going to be a lot of work and dynamic sql to even get close. You only mention string and number datatypes. What about dates or other various datatypes? Do you simply skip those?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mxy
mxy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1530 Visits: 3972
thanks sena . i dont need to worry about rest of the columns in those tables. i can add another column to specify whether its a string or num column in admin table

this is to scramble business sensitive data.
mxy
mxy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1530 Visits: 3972
Can anyone help on this
!!!!!!!!!!!!!!!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search