Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

update columns dynamically Expand / Collapse
Author
Message
Posted Wednesday, October 23, 2013 8:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
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!!!
Post #1507656
Posted Wednesday, October 23, 2013 8:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1507663
Posted Wednesday, October 23, 2013 8:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
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

Post #1507673
Posted Wednesday, October 23, 2013 9:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 13,204, Visits: 12,684
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 Moden's 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)
Post #1507687
Posted Wednesday, October 23, 2013 9:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
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')


Post #1507702
Posted Wednesday, October 23, 2013 10:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 13,204, Visits: 12,684
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 Moden's 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)
Post #1507722
Posted Wednesday, October 23, 2013 11:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
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
Post #1507770
Posted Wednesday, October 23, 2013 12:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 13,204, Visits: 12,684
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 Moden's 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)
Post #1507781
Posted Wednesday, October 23, 2013 12:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
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.
Post #1507786
Posted Wednesday, October 23, 2013 9:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
Can anyone help on this
!!!!!!!!!!!!!!!!!
Post #1507866
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse