I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.
Example:
Current data: "Bloomfield, CT"
Needs to become: Bloomfield
In other words, I need to remove the left quote, and everything after (including) the comma.
there are dozens of different cities in the DB like this.
I can write the simple query that can pull out all of the data that has a comma, or quotes. What I don't seem to get is how to then "erase" the quotes (or the string that includes the comma and everything after it) and then update the DB with this new value.
Help?Most databases have a substring and in-string (or position string) function which you can use in combination to do parsing. My example is oriented to DB2:
SUBSTR(current_data, 2, POSSTR(current_data, ',') - 1)
Result would be Bloomfield
You can use the above in an UPDATE statement:
UPDATE table SET city = SUBSTR(current_data, 2, POSSTR(current_data, ',') - 1)
Originally posted by rexnervous
I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.
Example:
Current data: "Bloomfield, CT"
Needs to become: Bloomfield
In other words, I need to remove the left quote, and everything after (including) the comma.
there are dozens of different cities in the DB like this.
I can write the simple query that can pull out all of the data that has a comma, or quotes. What I don't seem to get is how to then "erase" the quotes (or the string that includes the comma and everything after it) and then update the DB with this new value.
Help?|||Also, use the 'REPLACE' and 'GRATER' functions to eliminate the quotes:
UPDATE mytable
SET city = REPLACE(SUBSTR(city, 1
,GREATER(POSSTR(city, ',') - 1,LENGTH(city)))
,'"','')
WHERE POSSTR(city, ',') > 0
OR POSSTR(city, '"') > 0
:cool:|||Thanks you both, will give it a shot. Unfortunately, I'm using MS Access and it doesn't recognize those particular functions, but I think I can replace them.|||For Access take a look at Instr and Mid there is even a Replace. Have fun!
Originally posted by rexnervous
Thanks you both, will give it a shot. Unfortunately, I'm using MS Access and it doesn't recognize those particular functions, but I think I can replace them.
Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
Dynamically delete entire columns values
Hello all,
I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.
The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.
I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.
If anyone has any idea how to do this, I would greatly appreciate it.
Thanks in advance!This smells of dynamic SQL and queries against system tables to me.
-PatP|||If you implement your solution through a stored procedure, - it'll actually not require a dynamic SQL. But let's get something straight and agree to it, - it's an UPDATE we're talking about here, not a DELETE. Probably something like this would work:
create procedure dbo.sp_clear_fields (
@.string varchar(8000) = null,
@.delimiter char(1) = ',' )
as
if @.string is null return (0)
declare @.spos int, @.pos int
declare @.tbl table (
RecordID int identity(1,1) not null primary key clustered,
[Field] varchar(128) not null )
set @.string = replace(
replace(@.string, @.delimiter + ' ', @.delimiter), ' ' + @.delimiter, @.delimiter)
set @.spos = 1
set @.pos = 100
while (@.pos) > 0 begin
set @.pos = charindex(@.delimiter, @.string, @.spos)
insert @.tbl ([Field])
select
ltrim (
rtrim (
substring (
@.string, @.spos,
case
when (@.pos - @.spos) <= 0 then datalength(@.string) + 1
else @.pos
end - @.spos
)
)
)
set @.spos = @.pos + 1
end
update a
set Field1 = case when [Field] = 'Field1' then null else Field1 end,
Field2 = case when [Field] = 'Field2' then null else Field2,
Field3 = case when [Field] = 'Field3' then null else Field3,
Field4 = case when [Field] = 'Field4' then null else Field4,
Field5 = case when [Field] = 'Field5' then null else Field5
from TableA a, @.tbl
if @.@.error != 0 raiserror ('Failed to clear the fields!', 15, 1)
return (0)
go|||I'm with you thinking the question means that they want an update to NULL, but I think because of the dynamic nature of the query (the exrpessed need to pick up new columns that are added after the fact) that dynamic SQL is required. Even if it wasn't required because of that, I would find it preferable just because of the much lower load on the server (one pass through the table as opposed to one pass for each column being NULLed). I'd use something like:CREATE TABLE tMtrebs (
MtrebsID INT NOT NULL
CONSTRAINT XPKtMtrebs
PRIMARY KEY (MtrebsID)
, col1 VARCHAR(20) NULL
, col2 INT NULL
, col3 DATETIME NULL
, col4 VARCHAR(20) NULL
, col5 DATETIME NOT NULL
, col6 INT NOT NULL
)
GO
DECLARE @.cCmd VARCHAR(500)
SELECT @.cCmd = Coalesce(@.cCmd + ', [' + sc.name + '] = NULL'
, 'UPDATE tMtrebs SET [' + sc.name + '] = NULL')
FROM dbo.syscolumns AS sc
WHERE Object_id('dbo.tMtrebs') = sc.id
AND sc.name NOT IN ('col4', 'col5') -- protected columns
AND 1 = ColumnProperty(Object_Id('dbo.tMtrebs'), sc.name, 'AllowsNull')
EXECUTE (@.cCmd)-PatP|||I agree that this makes no sense|||I agree that this makes no senseOh, and since when have minor details like that bothered any of us in the least?!?!
-PatP
I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.
The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.
I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.
If anyone has any idea how to do this, I would greatly appreciate it.
Thanks in advance!This smells of dynamic SQL and queries against system tables to me.
-PatP|||If you implement your solution through a stored procedure, - it'll actually not require a dynamic SQL. But let's get something straight and agree to it, - it's an UPDATE we're talking about here, not a DELETE. Probably something like this would work:
create procedure dbo.sp_clear_fields (
@.string varchar(8000) = null,
@.delimiter char(1) = ',' )
as
if @.string is null return (0)
declare @.spos int, @.pos int
declare @.tbl table (
RecordID int identity(1,1) not null primary key clustered,
[Field] varchar(128) not null )
set @.string = replace(
replace(@.string, @.delimiter + ' ', @.delimiter), ' ' + @.delimiter, @.delimiter)
set @.spos = 1
set @.pos = 100
while (@.pos) > 0 begin
set @.pos = charindex(@.delimiter, @.string, @.spos)
insert @.tbl ([Field])
select
ltrim (
rtrim (
substring (
@.string, @.spos,
case
when (@.pos - @.spos) <= 0 then datalength(@.string) + 1
else @.pos
end - @.spos
)
)
)
set @.spos = @.pos + 1
end
update a
set Field1 = case when [Field] = 'Field1' then null else Field1 end,
Field2 = case when [Field] = 'Field2' then null else Field2,
Field3 = case when [Field] = 'Field3' then null else Field3,
Field4 = case when [Field] = 'Field4' then null else Field4,
Field5 = case when [Field] = 'Field5' then null else Field5
from TableA a, @.tbl
if @.@.error != 0 raiserror ('Failed to clear the fields!', 15, 1)
return (0)
go|||I'm with you thinking the question means that they want an update to NULL, but I think because of the dynamic nature of the query (the exrpessed need to pick up new columns that are added after the fact) that dynamic SQL is required. Even if it wasn't required because of that, I would find it preferable just because of the much lower load on the server (one pass through the table as opposed to one pass for each column being NULLed). I'd use something like:CREATE TABLE tMtrebs (
MtrebsID INT NOT NULL
CONSTRAINT XPKtMtrebs
PRIMARY KEY (MtrebsID)
, col1 VARCHAR(20) NULL
, col2 INT NULL
, col3 DATETIME NULL
, col4 VARCHAR(20) NULL
, col5 DATETIME NOT NULL
, col6 INT NOT NULL
)
GO
DECLARE @.cCmd VARCHAR(500)
SELECT @.cCmd = Coalesce(@.cCmd + ', [' + sc.name + '] = NULL'
, 'UPDATE tMtrebs SET [' + sc.name + '] = NULL')
FROM dbo.syscolumns AS sc
WHERE Object_id('dbo.tMtrebs') = sc.id
AND sc.name NOT IN ('col4', 'col5') -- protected columns
AND 1 = ColumnProperty(Object_Id('dbo.tMtrebs'), sc.name, 'AllowsNull')
EXECUTE (@.cCmd)-PatP|||I agree that this makes no sense|||I agree that this makes no senseOh, and since when have minor details like that bothered any of us in the least?!?!
-PatP
dynamically delete data
Hi All,
I have the following situation.
Every month, I populate data from a source table.
This table has a field called process_date (char data type) and the
format is mmyy. So, 0406 means data for the month of April of 2006.
This source table always overlaps with old data. For example, for this
month it may have data for January, February or March of 2006, which I
already have processed.
What I do presently is I manually run a delete command and then insert
in the target table.
Such as:
delete Table1 where Process_Date<>'0406'
I want to make this automated so that I will not have to manually run
the above code.
I was wondering how could I achieve that?
I will highly appreciate your help.
Thanks a million in advance.
Best regards,
MamunHello Mamun,
You could create a SQL Server Agent job to run every month. This job
can execute the T-SQL statements you require to insert/delete the
required data and won't require any intervention by you (although you
should be checking that whenever the job executes it executes
successfully).
If you're new to creating SQL Server Agent jobs then SQL Server Books
Online should be able to run you through the process.
HTH,
Nate.
mamun wrote:
> Hi All,
> I have the following situation.
> Every month, I populate data from a source table.
> This table has a field called process_date (char data type) and the
> format is mmyy. So, 0406 means data for the month of April of 2006.
> This source table always overlaps with old data. For example, for this
> month it may have data for January, February or March of 2006, which I
> already have processed.
> What I do presently is I manually run a delete command and then insert
> in the target table.
> Such as:
> delete Table1 where Process_Date<>'0406'
> I want to make this automated so that I will not have to manually run
> the above code.
> I was wondering how could I achieve that?
> I will highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun
I have the following situation.
Every month, I populate data from a source table.
This table has a field called process_date (char data type) and the
format is mmyy. So, 0406 means data for the month of April of 2006.
This source table always overlaps with old data. For example, for this
month it may have data for January, February or March of 2006, which I
already have processed.
What I do presently is I manually run a delete command and then insert
in the target table.
Such as:
delete Table1 where Process_Date<>'0406'
I want to make this automated so that I will not have to manually run
the above code.
I was wondering how could I achieve that?
I will highly appreciate your help.
Thanks a million in advance.
Best regards,
MamunHello Mamun,
You could create a SQL Server Agent job to run every month. This job
can execute the T-SQL statements you require to insert/delete the
required data and won't require any intervention by you (although you
should be checking that whenever the job executes it executes
successfully).
If you're new to creating SQL Server Agent jobs then SQL Server Books
Online should be able to run you through the process.
HTH,
Nate.
mamun wrote:
> Hi All,
> I have the following situation.
> Every month, I populate data from a source table.
> This table has a field called process_date (char data type) and the
> format is mmyy. So, 0406 means data for the month of April of 2006.
> This source table always overlaps with old data. For example, for this
> month it may have data for January, February or March of 2006, which I
> already have processed.
> What I do presently is I manually run a delete command and then insert
> in the target table.
> Such as:
> delete Table1 where Process_Date<>'0406'
> I want to make this automated so that I will not have to manually run
> the above code.
> I was wondering how could I achieve that?
> I will highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun
Dynamically delete a row from report
Hi,
I have a report containing some values. Surprise? :)
I want to delete rows containing values 0 and null from the report. I need
to include those rows to the sql query, but don't wan't them to be seen in
that particular table. Can the dynamic deletion of a row be done?
Thanks for helping the noobie!No problem anymore, I think.
I don't have to delete the row, instead I just use the filter in the table
to exclude the values I don't want to be shown...
I have a report containing some values. Surprise? :)
I want to delete rows containing values 0 and null from the report. I need
to include those rows to the sql query, but don't wan't them to be seen in
that particular table. Can the dynamic deletion of a row be done?
Thanks for helping the noobie!No problem anymore, I think.
I don't have to delete the row, instead I just use the filter in the table
to exclude the values I don't want to be shown...
Friday, March 9, 2012
dynamically columns
Hi guys,
Is there any method to add/delete dynamically columns in a table, not in a
matrix?
thanks,
RaduYou can not really Dynamically add columns to a table..
But you can pre-create a number of extra columns, show/hide and assign their
values on the fly...
That's about the best we can do now.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radu" <Radu@.discussions.microsoft.com> wrote in message
news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> Hi guys,
> Is there any method to add/delete dynamically columns in a table, not in a
> matrix?
> thanks,
> Radu|||Just another thought... ONe of the things we do that 'sort of' simluates
dynamically adding columns is that we will create a single column, then
populate it with varying multiple pieces of information concatenated
together as a single sql column...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radu" <Radu@.discussions.microsoft.com> wrote in message
news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> Hi guys,
> Is there any method to add/delete dynamically columns in a table, not in a
> matrix?
> thanks,
> Radu|||You could build up the XML for the report programmatically. This would allow
you complete control over what columns get placed in your table. A possible
disadvantage is that you would have to deploy the report programmatically
once it had been built up.|||snyder,
i want to hide a whole column in a table if there is no data to be
displayed in the entire column, if there exists atleast one record in the
column then we need to show the column else hide it.
"Wayne Snyder" wrote:
> You can not really Dynamically add columns to a table..
> But you can pre-create a number of extra columns, show/hide and assign their
> values on the fly...
> That's about the best we can do now.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radu" <Radu@.discussions.microsoft.com> wrote in message
> news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> > Hi guys,
> >
> > Is there any method to add/delete dynamically columns in a table, not in a
> > matrix?
> >
> > thanks,
> > Radu
>
>|||how do you create a single column in the matrix control and then populate it
with multiple pieces of information
I amtrying to add a Percent Change Col to Pivoted colmns in Matrix
"Wayne Snyder" wrote:
> Just another thought... ONe of the things we do that 'sort of' simluates
> dynamically adding columns is that we will create a single column, then
> populate it with varying multiple pieces of information concatenated
> together as a single sql column...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radu" <Radu@.discussions.microsoft.com> wrote in message
> news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> > Hi guys,
> >
> > Is there any method to add/delete dynamically columns in a table, not in a
> > matrix?
> >
> > thanks,
> > Radu
>
>
Is there any method to add/delete dynamically columns in a table, not in a
matrix?
thanks,
RaduYou can not really Dynamically add columns to a table..
But you can pre-create a number of extra columns, show/hide and assign their
values on the fly...
That's about the best we can do now.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radu" <Radu@.discussions.microsoft.com> wrote in message
news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> Hi guys,
> Is there any method to add/delete dynamically columns in a table, not in a
> matrix?
> thanks,
> Radu|||Just another thought... ONe of the things we do that 'sort of' simluates
dynamically adding columns is that we will create a single column, then
populate it with varying multiple pieces of information concatenated
together as a single sql column...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radu" <Radu@.discussions.microsoft.com> wrote in message
news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> Hi guys,
> Is there any method to add/delete dynamically columns in a table, not in a
> matrix?
> thanks,
> Radu|||You could build up the XML for the report programmatically. This would allow
you complete control over what columns get placed in your table. A possible
disadvantage is that you would have to deploy the report programmatically
once it had been built up.|||snyder,
i want to hide a whole column in a table if there is no data to be
displayed in the entire column, if there exists atleast one record in the
column then we need to show the column else hide it.
"Wayne Snyder" wrote:
> You can not really Dynamically add columns to a table..
> But you can pre-create a number of extra columns, show/hide and assign their
> values on the fly...
> That's about the best we can do now.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radu" <Radu@.discussions.microsoft.com> wrote in message
> news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> > Hi guys,
> >
> > Is there any method to add/delete dynamically columns in a table, not in a
> > matrix?
> >
> > thanks,
> > Radu
>
>|||how do you create a single column in the matrix control and then populate it
with multiple pieces of information
I amtrying to add a Percent Change Col to Pivoted colmns in Matrix
"Wayne Snyder" wrote:
> Just another thought... ONe of the things we do that 'sort of' simluates
> dynamically adding columns is that we will create a single column, then
> populate it with varying multiple pieces of information concatenated
> together as a single sql column...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radu" <Radu@.discussions.microsoft.com> wrote in message
> news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> > Hi guys,
> >
> > Is there any method to add/delete dynamically columns in a table, not in a
> > matrix?
> >
> > thanks,
> > Radu
>
>
Wednesday, February 15, 2012
Dynamic SQL delete query problem
Hi all--Given a query that returns ~557K rows on SQL Server 2005 SP1:
SELECT distinct(a.[Import_Date]),b.[Import_Date],
a.[OS_USERNAME],b.[OS_USERNAME],a.[USERNAME],b.[USERNAME],
a.[TIMESTAMP],b.[TIMESTAMP]
FROM [DBA_AUDIT_SESSION] a, [DBA_AUDIT_SESSION] b
where a.[OS_USERNAME]=b.[OS_USERNAME]
and a.[USERNAME]=b.[USERNAME]
and a.[TIMESTAMP]=b.[TIMESTAMP]
and a.[Import_Date]<b.[Import_Date]
On a table that has the following definition:
TABLE [dbo].[DBA_AUDIT_SESSION](
[Import_Date] [datetime] NULL,
[INSTANCE_NAME] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOST_NAME] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OS_USERNAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USERNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USERHOST] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TERMINAL] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIMESTAMP] [datetime] NULL,
[ACTION_NAME] [varchar](27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOGOFF_TIME] [datetime] NULL,
[LOGOFF_LREAD] [float] NULL,
[LOGOFF_PREAD] [float] NULL,
[LOGOFF_LWRITE] [float] NULL,
[LOGOFF_DLOCK] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SESSIONID] [float] NULL,
[RETURNCODE] [float] NULL,
[CLIENT_ID] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SESSION_CPU] [float] NULL
I would like to delete duplicate data from this table and keep only the data with the latest Import_Date. I came up with the following delete statement:
delete FROM [DBA_AUDIT_SESSION]
FROM [DBA_AUDIT_SESSION] a,
inner join [DBA_AUDIT_SESSION] b
on a.[TIMESTAMP]=b.[TIMESTAMP]
where a.[Import_Date]<b.[Import_Date];
The command parses successfully, but I get the following runtime error:
'Table DBA_AUDIT_SESSION is ambiguous.'
Does anyone have suggestions on how to fix this delete statement?
Use the TSQL update/delete statement syntax sparingly. The TSQL specific syntax can produce unexpected results if used incorrectly or performance problems also. You can use the standard DELETE statement syntax like:
-- assumes that import_date is unique per timestamp
delete [DBA_AUDIT_SESSION]
where Import_Date < (select max(a.Import_Date) from [DBA_AUDIT_SESSION] as a
where a.TimeStamp = [DBA_AUDIT_SESSION].TimeStamp)
|||This seems to do the trick, and meets all the criteria for cleanup needed. Thanks!
Subscribe to:
Posts (Atom)