create table tbl1(
fld1 varchar(10),
fld2 varchar(10))
insert into tbl1 Values('joe','x')
insert into tbl1 Values('joe','y')
insert into tbl1 Values('joe','z')
insert into tbl1 Values('bill','x')
insert into tbl1 Values('sam','z')
insert into tbl1 Values('ted','x')
insert into tbl1 Values('ted','y')
insert into tbl1 Values('ed','x')
insert into tbl1 Values('mary','x')
insert into tbl1 Values('sue','z')
insert into tbl1 Values('tom','x')
insert into tbl1 Values('tom','y')
insert into tbl1 Values('tom','z')
insert into tbl1 Values('frank','x')
insert into tbl1 Values('samir','x')
insert into tbl1 Values('li','x')
insert into tbl1 Values('li','z')
insert into tbl1 Values('cindy','z')
insert into tbl1 Values('bert','x')
insert into tbl1 Values('jon','x')
--the statement below contains duplicate records.
SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 )
--I want to remove/delete the records from tbl1 where fld1 has count(fld1)
> 1 and fld2 = 'z' -- joe, x, joe, y, joe, z| tom, x, tom, y, tom, z| li, x,[/color
]
li, z.
joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld2.
I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
remove rows where count(fld1)>1 and fld2 = 'z'.
--Here is what I tried that did not work:
Delete from tbl1
Where fld2 in
(SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 ))
and fld2 = 'z'
Any suggestions appreciated how this could be accomplished.
Thanks,
RichTry THis:
Delete tbl1
Where fld1 In
(SELECT fld1
FROM tbl1
GROUP BY fld1
HAVING Count(*)>1 )
And Fld2 = 'z'
"Rich" wrote:
> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich|||Thanks very much.
"CBretana" wrote:
> Try THis:
> Delete tbl1
> Where fld1 In
> (SELECT fld1
> FROM tbl1
> GROUP BY fld1
> HAVING Count(*)>1 )
> And Fld2 = 'z'
> "Rich" wrote:
>|||And for the future, use UNIQUE constraints to ensure your data doesn't get
this way in the first place.
-- Alex
"Rich" wrote:
> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 29, 2012
Delete Duplicate Rec
HI EveryOne
this is the Qry to delete duplicate records from table(Database)
Can you explain the concept of single instance it is dangerous proposition.sql
I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com
Quote:
Originally Posted by amitmant
HI EveryOne
I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com
this is the Qry to delete duplicate records from table(Database)
===================QRY======================
delete from table1 where Field1=(select field1 from table1 group by field1 having count(field1)>1)|||This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.|||
Quote:
Originally Posted by iburyak
This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.
Can you explain the concept of single instance it is dangerous proposition.sql
delete duplicate orders
I have an order with multiple line items. When the order comes in and put into a table I would like to check that they haven't submitted it twice. Right now I have a stored procedure that can find duplicate lines, but I really need if the whole order ha
s been duplicated then delete it.
So in my store procedure I use:
SELECT COUNT(*) AS Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity
FROM tblItemOrder
GROUP BY ItemNumber,Store,DeliveryDate,submission,Quantity
HAVING COUNT(*) > 1
So this finds duplicate rows. But I need to find if the all rows are duplicated go ahead and delete.
The table structure is fields Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity with no primary key (working table to get the data into shape).
Even if I could find if there are the same amount of rows that have duplicates COUNT(*) > 1, then go ahead and delete the duplicates that would work fine.
hi ashley,
See following example:
--sample data.
create table #cartype(manufacturer varchar(500), itemnumber int)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',3)
insert into #cartype values('GE',3)
--deleting all duplicate rows from the table, query will be.
delete a
from #cartype a join
(select manufacturer, itemnumber
from #cartype
group by manufacturer, itemnumber
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.itemnumber = b.itemnumber
--if you want to keep one row out of the duplicate rows, you will have to
add an identity column to the table.
Ex:
alter table #cartype add idd int identity
--deleting duplicate rows from the table except one
delete from #cartype
where not exists
(Select * from #cartype a
where a.manufacturer = #cartype.manufacturer
and a.itemnumber = #cartype.itemnumber
having min(idd) = #cartype.idd)
--drop the temporary added column
alter table #cartype drop column idd
Vishal Parkar
vgparkar@.yahoo.co.in
s been duplicated then delete it.
So in my store procedure I use:
SELECT COUNT(*) AS Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity
FROM tblItemOrder
GROUP BY ItemNumber,Store,DeliveryDate,submission,Quantity
HAVING COUNT(*) > 1
So this finds duplicate rows. But I need to find if the all rows are duplicated go ahead and delete.
The table structure is fields Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity with no primary key (working table to get the data into shape).
Even if I could find if there are the same amount of rows that have duplicates COUNT(*) > 1, then go ahead and delete the duplicates that would work fine.
hi ashley,
See following example:
--sample data.
create table #cartype(manufacturer varchar(500), itemnumber int)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',3)
insert into #cartype values('GE',3)
--deleting all duplicate rows from the table, query will be.
delete a
from #cartype a join
(select manufacturer, itemnumber
from #cartype
group by manufacturer, itemnumber
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.itemnumber = b.itemnumber
--if you want to keep one row out of the duplicate rows, you will have to
add an identity column to the table.
Ex:
alter table #cartype add idd int identity
--deleting duplicate rows from the table except one
delete from #cartype
where not exists
(Select * from #cartype a
where a.manufacturer = #cartype.manufacturer
and a.itemnumber = #cartype.itemnumber
having min(idd) = #cartype.idd)
--drop the temporary added column
alter table #cartype drop column idd
Vishal Parkar
vgparkar@.yahoo.co.in
Delete duplicate entry
Hi guys,
How can we delete duplicate entries in a table. please send me a
query which delete duplicate entry from table.Manish Sukhija wrote:
> How can we delete duplicate entries in a table. please
> send me a query which delete duplicate entry from table.
This has been discussed a couple of times the last w
s:
http://shurl.org/BrNkD
HTH,
Stijn Verrept.
How can we delete duplicate entries in a table. please send me a
query which delete duplicate entry from table.Manish Sukhija wrote:
> How can we delete duplicate entries in a table. please
> send me a query which delete duplicate entry from table.
This has been discussed a couple of times the last w
s:http://shurl.org/BrNkD
HTH,
Stijn Verrept.
Delete Duplicate
SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>sql
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>sql
Delete Duplicate
SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John B
Hi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||One of the ways:
http://support.microsoft.com/default...b;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John B
Hi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||One of the ways:
http://support.microsoft.com/default...b;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>
Delete Duplicate
SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>
Delete Duplicate
SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>> SQL 2K
>> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
>> NAME,
>> SUPVR, MOD_DATE
>> I want to retain just one row for unique (ID, DPTID) combination, doesn't
>> matter with values of rest of the columns.
>> Thanks In Advance
>> John B
>>
>>
>>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
--
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> > Hi
> >
> > Maybe if mod_date is unique for each id,dptid then
> >
> > DELETE FROM DptAct
> > WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> > DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> >
> > John
> >
> > "MS User" wrote:
> >
> >> SQL 2K
> >>
> >> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
> >> NAME,
> >> SUPVR, MOD_DATE
> >>
> >> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> >> matter with values of rest of the columns.
> >>
> >> Thanks In Advance
> >>
> >> John B
> >>
> >>
> >>
> >>
> >>
> >>
>
>
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>> SQL 2K
>> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
>> NAME,
>> SUPVR, MOD_DATE
>> I want to retain just one row for unique (ID, DPTID) combination, doesn't
>> matter with values of rest of the columns.
>> Thanks In Advance
>> John B
>>
>>
>>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
--
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> > Hi
> >
> > Maybe if mod_date is unique for each id,dptid then
> >
> > DELETE FROM DptAct
> > WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> > DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> >
> > John
> >
> > "MS User" wrote:
> >
> >> SQL 2K
> >>
> >> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
> >> NAME,
> >> SUPVR, MOD_DATE
> >>
> >> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> >> matter with values of rest of the columns.
> >>
> >> Thanks In Advance
> >>
> >> John B
> >>
> >>
> >>
> >>
> >>
> >>
>
>
Delete Dates > 6 months
Hello,
I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.
I am unable to find the statement that will execute this operation.
Anyone has an idea please.
Thanks
Markdelete from Log where Recorded_date < dateadd(month, -6, getdate())
Assuming you did record the datetime in your table.|||You mean like:
DELETE FROM yourTable
WHERE DATEDIFF(d,DateCole,GetDate()) > 180|||delete from Log where Recorded_date < dateadd(month, -6, getdate())
Assuming you did record the datetime in your table.
Be careful about months...anywhere in a month counts...
I like days better...
No?|||Be careful about months...anywhere in a month counts...
I like days better...
No?
But when you adjust by months, it just advances/decreases the month value of the date, maintaining the same day value (unless you are advancing/decreasing from a month with 31 days to a month with 30 days, then the days are adjusted down by one). When you adjust by days, you can't take into account 30 days verses 31 days. Adding 30 days to the current date (5/6/2004) you get 6/5/2004. Adding 1 month to the current date gives you 6/6/2004. I've never seen SQL Server arbitrarily select a day when adjusting by months.
When you execute these two statements:
SELECT DATEADD(m, -6, GETDATE())
SELECT DATEADD(d, -180, GETDATE())
You get two very different results:
2003-11-06 13:24:14.700
2003-11-08 13:24:14.700|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.|||Datediff returns integer values, but the DateAdd function is acceptable. I'd use:
where record_date < dateadd (month, -6, getdate())
...since the poster did specify months.|||I agree with Brett I think. I think working with days is better and safer than months. 6 months makes about 182 days.
I must say that I did not expect so many responses in that short laps of time. I'll try what you all said and let you know.
Thanks
Mark|||Both are equally "safe". It's just a matter of whether you specs call for X months or X days.|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.
I'd still go with Days...
But the above is THE most important point...
My Original would cause a stage 2 predicat (nonsargable).
Very Bad
Would cause a scan...
What he hell wa I thinking (And there you go...not thinking...AGAIN...I guess the need to focus never arose *)
* JBuffet
Thanks for pointing that out|||I think there are times when you need to calculate by months, and times you need to calculate by days. I think blindman put it right, it depends on the requirements of the project. If I need to do something every 30 days, regardless of the month, then I'd adjust by days, but if I need to do something on a specific day six months from now, I'd use months. Otherwise I'd have to find out which months have 30 days, and which have 31, (or 29 for a leap year) then add them together before I use dateadd() by day.
By just adjusting by days you'd be off 7 days on average per year, or 5 if you're adjusting by 31 (not counting leap years), unless you manually compensate.|||In Fact I used months... It works fine and suits my needs.
DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6
Thanks a lot to all of you who posted.
Mark|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.
Ahhhhh...I seem to remember something about this from my old SQL days...Can you explain, or point me in the direction of where I can figure out the REASON the index would be invalidated? Or is it simply because the function call would effectively "hide" the table column being considered?
I think the gist of this is that the index column needs to be "accessable to the query" - i.e., the optimizer can't "see" into the function to know the target column is there?
*LOL* I THINK I understand, but probably not regurgitating it in a palatable way (now THAT's just GROSS!!!)|||In Fact I used months... It works fine and suits my needs.
DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6
Thanks a lot to all of you who posted.
Mark
Read back in some of these posts... They make mention of doing the SQL this way invalidates the index. It would be better to do:
DELETE FROM Log WHERE DateL < DATEADD(mm, -6, GETDATE())|||"dateadd (dd, -180, getdate())" need only be evaluated once. It returns the same value for each row, which can then be matched against an index in the table.
"DATEDIFF(mm, DateL, GETDATE()) > 6" must be evaluated separately for each row, because the rows can and probable do return different results.|||and the reason a function call must be evaluated for each value, is because how do you know what month a date of 28488 is?
dates are stored as integers
an index on a date column is an index of integers, and if you can't decide whether an index value is one you want without doing a function on it, the optimizer says "sheesh, if i gotta evaluate all index values, i might as well just scan the table"
dateadd (dd,-180,getdate()), as blindman pointed out, gets evaluated once, prior to query execution, and its value, which happens to be 37933, can then be used in an efficient index search|||Outstanding guys...well done, and in "wow, I haven't done SQL work for 5 years or so, but I THINK I remember something about that..." language!
But now I need to go back and look at all the query work in my project *LOL*sql
I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.
I am unable to find the statement that will execute this operation.
Anyone has an idea please.
Thanks
Markdelete from Log where Recorded_date < dateadd(month, -6, getdate())
Assuming you did record the datetime in your table.|||You mean like:
DELETE FROM yourTable
WHERE DATEDIFF(d,DateCole,GetDate()) > 180|||delete from Log where Recorded_date < dateadd(month, -6, getdate())
Assuming you did record the datetime in your table.
Be careful about months...anywhere in a month counts...
I like days better...
No?|||Be careful about months...anywhere in a month counts...
I like days better...
No?
But when you adjust by months, it just advances/decreases the month value of the date, maintaining the same day value (unless you are advancing/decreasing from a month with 31 days to a month with 30 days, then the days are adjusted down by one). When you adjust by days, you can't take into account 30 days verses 31 days. Adding 30 days to the current date (5/6/2004) you get 6/5/2004. Adding 1 month to the current date gives you 6/6/2004. I've never seen SQL Server arbitrarily select a day when adjusting by months.
When you execute these two statements:
SELECT DATEADD(m, -6, GETDATE())
SELECT DATEADD(d, -180, GETDATE())
You get two very different results:
2003-11-06 13:24:14.700
2003-11-08 13:24:14.700|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.|||Datediff returns integer values, but the DateAdd function is acceptable. I'd use:
where record_date < dateadd (month, -6, getdate())
...since the poster did specify months.|||I agree with Brett I think. I think working with days is better and safer than months. 6 months makes about 182 days.
I must say that I did not expect so many responses in that short laps of time. I'll try what you all said and let you know.
Thanks
Mark|||Both are equally "safe". It's just a matter of whether you specs call for X months or X days.|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.
I'd still go with Days...
But the above is THE most important point...
My Original would cause a stage 2 predicat (nonsargable).
Very Bad
Would cause a scan...
What he hell wa I thinking (And there you go...not thinking...AGAIN...I guess the need to focus never arose *)
* JBuffet
Thanks for pointing that out|||I think there are times when you need to calculate by months, and times you need to calculate by days. I think blindman put it right, it depends on the requirements of the project. If I need to do something every 30 days, regardless of the month, then I'd adjust by days, but if I need to do something on a specific day six months from now, I'd use months. Otherwise I'd have to find out which months have 30 days, and which have 31, (or 29 for a leap year) then add them together before I use dateadd() by day.
By just adjusting by days you'd be off 7 days on average per year, or 5 if you're adjusting by 31 (not counting leap years), unless you manually compensate.|||In Fact I used months... It works fine and suits my needs.
DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6
Thanks a lot to all of you who posted.
Mark|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.
Ahhhhh...I seem to remember something about this from my old SQL days...Can you explain, or point me in the direction of where I can figure out the REASON the index would be invalidated? Or is it simply because the function call would effectively "hide" the table column being considered?
I think the gist of this is that the index column needs to be "accessable to the query" - i.e., the optimizer can't "see" into the function to know the target column is there?
*LOL* I THINK I understand, but probably not regurgitating it in a palatable way (now THAT's just GROSS!!!)|||In Fact I used months... It works fine and suits my needs.
DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6
Thanks a lot to all of you who posted.
Mark
Read back in some of these posts... They make mention of doing the SQL this way invalidates the index. It would be better to do:
DELETE FROM Log WHERE DateL < DATEADD(mm, -6, GETDATE())|||"dateadd (dd, -180, getdate())" need only be evaluated once. It returns the same value for each row, which can then be matched against an index in the table.
"DATEDIFF(mm, DateL, GETDATE()) > 6" must be evaluated separately for each row, because the rows can and probable do return different results.|||and the reason a function call must be evaluated for each value, is because how do you know what month a date of 28488 is?
dates are stored as integers
an index on a date column is an index of integers, and if you can't decide whether an index value is one you want without doing a function on it, the optimizer says "sheesh, if i gotta evaluate all index values, i might as well just scan the table"
dateadd (dd,-180,getdate()), as blindman pointed out, gets evaluated once, prior to query execution, and its value, which happens to be 37933, can then be used in an efficient index search|||Outstanding guys...well done, and in "wow, I haven't done SQL work for 5 years or so, but I THINK I remember something about that..." language!
But now I need to go back and look at all the query work in my project *LOL*sql
Delete Data without impacting Tempdb
This is a SQL 2005 production server.
I have to delete around 51 million rows from a table which has 149 million rows.
Can't use truncate option as the other rows in the table are still needed.
How can I delete the rows without filling up the tempdb ?
If the tempdb fills up I can't bounce the server.Set your recovery mode to Simple so you don't log all the deletes. Consider deleting your data in smaller batches.|||I've been doing this exact thing for the past week, and did exactly that.
Just remember to defrag/rebuild indexes following this.|||tempdb? maybe you need to check whether you are trying to support snapshot isolation. In that case, the deleted rows will end up in tempdb prior to the commit.|||The recovery mode is "simple". Its still putting all the transaction in the log file.
The Tempdb detail was an error from my side.|||in that case, simply break the delete down into smaller chunks. The entire delete will always be logged. So instead of one big delete, make it 10 smaller deletes.|||thanks for all the responses.
The only solution seems to delete in small batches and then truncate the log.|||You should not need to truncate the log in simple recovery mode.|||Somehow it seems that some of the transaction log keeps getting held after large transactions. I find running checkpoint in the affected database brings things back in line. Checkpoint is also a side effect of truncating the log.|||Mcrowley,
That is exactly the issue I have. I have to truncate the log after doing the large delete inspite of recovery mode simple.|||After your done truncate with no_log and dbcc shrinkfile it back down.|||Bear in mind if you are performing this delete operation during normal day time it will have stress on tempdb too in addition to user database's transaction log.
So better to perform this operation during less traffic hours and also perform CHECKPOINT after couple of thousand rows to keep up the log size.
I have to delete around 51 million rows from a table which has 149 million rows.
Can't use truncate option as the other rows in the table are still needed.
How can I delete the rows without filling up the tempdb ?
If the tempdb fills up I can't bounce the server.Set your recovery mode to Simple so you don't log all the deletes. Consider deleting your data in smaller batches.|||I've been doing this exact thing for the past week, and did exactly that.
Just remember to defrag/rebuild indexes following this.|||tempdb? maybe you need to check whether you are trying to support snapshot isolation. In that case, the deleted rows will end up in tempdb prior to the commit.|||The recovery mode is "simple". Its still putting all the transaction in the log file.
The Tempdb detail was an error from my side.|||in that case, simply break the delete down into smaller chunks. The entire delete will always be logged. So instead of one big delete, make it 10 smaller deletes.|||thanks for all the responses.
The only solution seems to delete in small batches and then truncate the log.|||You should not need to truncate the log in simple recovery mode.|||Somehow it seems that some of the transaction log keeps getting held after large transactions. I find running checkpoint in the affected database brings things back in line. Checkpoint is also a side effect of truncating the log.|||Mcrowley,
That is exactly the issue I have. I have to truncate the log after doing the large delete inspite of recovery mode simple.|||After your done truncate with no_log and dbcc shrinkfile it back down.|||Bear in mind if you are performing this delete operation during normal day time it will have stress on tempdb too in addition to user database's transaction log.
So better to perform this operation during less traffic hours and also perform CHECKPOINT after couple of thousand rows to keep up the log size.
delete data from a field in a table
Hi,
I am trying to delete data from a field in my table, I want to clear all the
data from one field in my table (assuming the table is Students and the
field is firstName)
Thank you in advance.
LamyDo you want to set the column to NULL or an empty string? In any case, it is
a simple UPDATE
statements:
UPDATE tblname
SET colname = NULL
or
UPDATE tblname
SET colname = ''
Note that both above will modify all rows. Add a WHERE clause of you want to
limit.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Lamy" <lamine_d@.mpt-ltd.com> wrote in message news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl.
.
> Hi,
> I am trying to delete data from a field in my table, I want to clear all t
he data from one field
> in my table (assuming the table is Students and the field is firstName)
> Thank you in advance.
> Lamy
>|||If the column you need to delete is nullable, you simply can:
UPDATE Students
SET firstName = NULL
If the column isn't nullable you could
UPDATE Students
SET firstName = expression | DEFAULT
if some default is defined you can use it; expression is any expression
resulting in an empty field. Remember that NULL is different from "empty" or
blank.
Gilberto
"Lamy" wrote:
> Hi,
> I am trying to delete data from a field in my table, I want to clear all t
he
> data from one field in my table (assuming the table is Students and the
> field is firstName)
> Thank you in advance.
> Lamy
>
>|||Dear Tibor and Gilberto,
Silly me.
Many thanks guys.
Regards
Lamine
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOru0t2dHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Do you want to set the column to NULL or an empty string? In any case, it
> is a simple UPDATE statements:
> UPDATE tblname
> SET colname = NULL
> or
> UPDATE tblname
> SET colname = ''
> Note that both above will modify all rows. Add a WHERE clause of you want
> to limit.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Lamy" <lamine_d@.mpt-ltd.com> wrote in message
> news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
>
I am trying to delete data from a field in my table, I want to clear all the
data from one field in my table (assuming the table is Students and the
field is firstName)
Thank you in advance.
LamyDo you want to set the column to NULL or an empty string? In any case, it is
a simple UPDATE
statements:
UPDATE tblname
SET colname = NULL
or
UPDATE tblname
SET colname = ''
Note that both above will modify all rows. Add a WHERE clause of you want to
limit.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Lamy" <lamine_d@.mpt-ltd.com> wrote in message news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl.
.
> Hi,
> I am trying to delete data from a field in my table, I want to clear all t
he data from one field
> in my table (assuming the table is Students and the field is firstName)
> Thank you in advance.
> Lamy
>|||If the column you need to delete is nullable, you simply can:
UPDATE Students
SET firstName = NULL
If the column isn't nullable you could
UPDATE Students
SET firstName = expression | DEFAULT
if some default is defined you can use it; expression is any expression
resulting in an empty field. Remember that NULL is different from "empty" or
blank.
Gilberto
"Lamy" wrote:
> Hi,
> I am trying to delete data from a field in my table, I want to clear all t
he
> data from one field in my table (assuming the table is Students and the
> field is firstName)
> Thank you in advance.
> Lamy
>
>|||Dear Tibor and Gilberto,
Silly me.
Many thanks guys.
Regards
Lamine
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOru0t2dHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Do you want to set the column to NULL or an empty string? In any case, it
> is a simple UPDATE statements:
> UPDATE tblname
> SET colname = NULL
> or
> UPDATE tblname
> SET colname = ''
> Note that both above will modify all rows. Add a WHERE clause of you want
> to limit.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Lamy" <lamine_d@.mpt-ltd.com> wrote in message
> news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
>
Tuesday, March 27, 2012
delete data from a field in a table
Hi,
I am trying to delete data from a field in my table, I want to clear all the
data from one field in my table (assuming the table is Students and the
field is firstName)
Thank you in advance.
LamyDo you want to set the column to NULL or an empty string? In any case, it is a simple UPDATE
statements:
UPDATE tblname
SET colname = NULL
or
UPDATE tblname
SET colname = ''
Note that both above will modify all rows. Add a WHERE clause of you want to limit.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Lamy" <lamine_d@.mpt-ltd.com> wrote in message news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am trying to delete data from a field in my table, I want to clear all the data from one field
> in my table (assuming the table is Students and the field is firstName)
> Thank you in advance.
> Lamy
>|||If the column you need to delete is nullable, you simply can:
UPDATE Students
SET firstName = NULL
If the column isn't nullable you could
UPDATE Students
SET firstName = expression | DEFAULT
if some default is defined you can use it; expression is any expression
resulting in an empty field. Remember that NULL is different from "empty" or
blank.
Gilberto
"Lamy" wrote:
> Hi,
> I am trying to delete data from a field in my table, I want to clear all the
> data from one field in my table (assuming the table is Students and the
> field is firstName)
> Thank you in advance.
> Lamy
>
>|||Dear Tibor and Gilberto,
Silly me.
Many thanks guys.
Regards
Lamine
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOru0t2dHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Do you want to set the column to NULL or an empty string? In any case, it
> is a simple UPDATE statements:
> UPDATE tblname
> SET colname = NULL
> or
> UPDATE tblname
> SET colname = ''
> Note that both above will modify all rows. Add a WHERE clause of you want
> to limit.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Lamy" <lamine_d@.mpt-ltd.com> wrote in message
> news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I am trying to delete data from a field in my table, I want to clear all
>> the data from one field in my table (assuming the table is Students and
>> the field is firstName)
>> Thank you in advance.
>> Lamy
>sql
I am trying to delete data from a field in my table, I want to clear all the
data from one field in my table (assuming the table is Students and the
field is firstName)
Thank you in advance.
LamyDo you want to set the column to NULL or an empty string? In any case, it is a simple UPDATE
statements:
UPDATE tblname
SET colname = NULL
or
UPDATE tblname
SET colname = ''
Note that both above will modify all rows. Add a WHERE clause of you want to limit.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Lamy" <lamine_d@.mpt-ltd.com> wrote in message news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am trying to delete data from a field in my table, I want to clear all the data from one field
> in my table (assuming the table is Students and the field is firstName)
> Thank you in advance.
> Lamy
>|||If the column you need to delete is nullable, you simply can:
UPDATE Students
SET firstName = NULL
If the column isn't nullable you could
UPDATE Students
SET firstName = expression | DEFAULT
if some default is defined you can use it; expression is any expression
resulting in an empty field. Remember that NULL is different from "empty" or
blank.
Gilberto
"Lamy" wrote:
> Hi,
> I am trying to delete data from a field in my table, I want to clear all the
> data from one field in my table (assuming the table is Students and the
> field is firstName)
> Thank you in advance.
> Lamy
>
>|||Dear Tibor and Gilberto,
Silly me.
Many thanks guys.
Regards
Lamine
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOru0t2dHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Do you want to set the column to NULL or an empty string? In any case, it
> is a simple UPDATE statements:
> UPDATE tblname
> SET colname = NULL
> or
> UPDATE tblname
> SET colname = ''
> Note that both above will modify all rows. Add a WHERE clause of you want
> to limit.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Lamy" <lamine_d@.mpt-ltd.com> wrote in message
> news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I am trying to delete data from a field in my table, I want to clear all
>> the data from one field in my table (assuming the table is Students and
>> the field is firstName)
>> Thank you in advance.
>> Lamy
>sql
Delete data before trigger executes
I am trying to delete data from a table prior to populating with new data via a trigger if certain critria matches to eliminate duplicates. I have copied the trigger below. The syntax checks ok but I get any error message saying 'Incorrect syntax near 'GO'. 'ALTER TRIGGER' must be the first statement in a query batch' when I try to save.
Can someone tell me if this is possible please.
IF EXISTS (SELECT * FROM hold_complete
WHERE fkey = hold_complete.fkey AND actiontext = 'hold' and Subactiontext = 'pending user')
delete from hold_complete where hold_complete.fkey = fkey
GO
CREATE TRIGGER tr_hold_complete ON CallsHistory
for INSERT AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert hold_complete
select ins.AddedDT, ins.fkey, ins.actiontext,
ins.subactiontext, con.emailaddress, ca.loggeddt,
(con.forename + ' ' + con.surname) as contactname,
ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
FROM inserted as ins with (nolock)
join calls as ca with (nolock)on
ins.fkey = ca.callid
join contact as con with (nolock) on
ca.contactid = con.contactid
join company as co with (nolock) on
ca.companyid = co.companyid
join callshistory as ch with (nolock) on
ins.historyid = ch.historyid
where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')Does the trigger tr_hold_complete already exist?|||Yes it does|||if it already exist you can't create a trigger with the same name. If you use alter trigger (and the same code), it will modify the trigger without changing anything and your code should work. Not sure if there might be a nicer implementation though|||There is only one trigger called tr_hold_complete. I am trying to modify the existing one that currently starts from CREATE TRIGGER (as per above) so that if a row already exists in the table that has the same fkey number as the updated record it deletes the existing record first then inserts the updated row.
Can that be done in a single trigger ?|||I think i see the error but a correct implementation eludes me at the moment and I have no access to books online or a sql server to check.
From the error i would guess that you can't have the syntax before the go statement when either creating or altering the trigger.
Not sure how to get round this one sorry.|||Originally posted by Bracksboy
There is only one trigger called tr_hold_complete. I am trying to modify the existing one that currently starts from CREATE TRIGGER (as per above) so that if a row already exists in the table that has the same fkey number as the updated record it deletes the existing record first then inserts the updated row.
Can that be done in a single trigger ?
read up on "instead of" triggers in Books online ... I think thats what you are looking for. am not near a sql server right now ... so you will have to look it up.|||Originally posted by Enigma
read up on "instead of" triggers in Books online ... I think thats what you are looking for. am not near a sql server right now ... so you will have to look it up.
Thanks Enigma. Spent most of the PM investigating INSTEAD OF but doesn't seem to do what I require either.
Can you actually do a Delete from where statement within a trigger 'cos I haven't found one in any of the examples I've searched today.|||Forget reading up on instead of triggers...there's just so much wrong here...
You do not want to do nolock...
and I don't think (damn that happend a lot) that a trigger will fire for any uncomitted data anyway...
The entire avenue your heading down shows that your trying to mess with things that you shouldn't
What are you trying to do...in non technical terms?
For example you mention you want to prevent dups...
ok, simple, put a contraint on the columns you would consider to be be dups...
what else?|||Originally posted by Brett Kaiser
What are you trying to do...in non technical terms?
For example you mention you want to prevent dups...
Brett
The original trigger populates a table that is used to automate emails from our call logging system (with VB). If a call is assigned a certain action (completed or pending user) then the trigger fires. Several mails are sent and if there is no movement on the call after a certain period the call is automatically closed using the VB app.
The problem I have is that the same call could be released but a few days later given the same action again and unless I can delete the original row the call will be closed on the original closure date.
Hope this makes it a bit clearer what i am trying to acheive
Can someone tell me if this is possible please.
IF EXISTS (SELECT * FROM hold_complete
WHERE fkey = hold_complete.fkey AND actiontext = 'hold' and Subactiontext = 'pending user')
delete from hold_complete where hold_complete.fkey = fkey
GO
CREATE TRIGGER tr_hold_complete ON CallsHistory
for INSERT AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert hold_complete
select ins.AddedDT, ins.fkey, ins.actiontext,
ins.subactiontext, con.emailaddress, ca.loggeddt,
(con.forename + ' ' + con.surname) as contactname,
ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
FROM inserted as ins with (nolock)
join calls as ca with (nolock)on
ins.fkey = ca.callid
join contact as con with (nolock) on
ca.contactid = con.contactid
join company as co with (nolock) on
ca.companyid = co.companyid
join callshistory as ch with (nolock) on
ins.historyid = ch.historyid
where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')Does the trigger tr_hold_complete already exist?|||Yes it does|||if it already exist you can't create a trigger with the same name. If you use alter trigger (and the same code), it will modify the trigger without changing anything and your code should work. Not sure if there might be a nicer implementation though|||There is only one trigger called tr_hold_complete. I am trying to modify the existing one that currently starts from CREATE TRIGGER (as per above) so that if a row already exists in the table that has the same fkey number as the updated record it deletes the existing record first then inserts the updated row.
Can that be done in a single trigger ?|||I think i see the error but a correct implementation eludes me at the moment and I have no access to books online or a sql server to check.
From the error i would guess that you can't have the syntax before the go statement when either creating or altering the trigger.
Not sure how to get round this one sorry.|||Originally posted by Bracksboy
There is only one trigger called tr_hold_complete. I am trying to modify the existing one that currently starts from CREATE TRIGGER (as per above) so that if a row already exists in the table that has the same fkey number as the updated record it deletes the existing record first then inserts the updated row.
Can that be done in a single trigger ?
read up on "instead of" triggers in Books online ... I think thats what you are looking for. am not near a sql server right now ... so you will have to look it up.|||Originally posted by Enigma
read up on "instead of" triggers in Books online ... I think thats what you are looking for. am not near a sql server right now ... so you will have to look it up.
Thanks Enigma. Spent most of the PM investigating INSTEAD OF but doesn't seem to do what I require either.
Can you actually do a Delete from where statement within a trigger 'cos I haven't found one in any of the examples I've searched today.|||Forget reading up on instead of triggers...there's just so much wrong here...
You do not want to do nolock...
and I don't think (damn that happend a lot) that a trigger will fire for any uncomitted data anyway...
The entire avenue your heading down shows that your trying to mess with things that you shouldn't
What are you trying to do...in non technical terms?
For example you mention you want to prevent dups...
ok, simple, put a contraint on the columns you would consider to be be dups...
what else?|||Originally posted by Brett Kaiser
What are you trying to do...in non technical terms?
For example you mention you want to prevent dups...
Brett
The original trigger populates a table that is used to automate emails from our call logging system (with VB). If a call is assigned a certain action (completed or pending user) then the trigger fires. Several mails are sent and if there is no movement on the call after a certain period the call is automatically closed using the VB app.
The problem I have is that the same call could be released but a few days later given the same action again and unless I can delete the original row the call will be closed on the original closure date.
Hope this makes it a bit clearer what i am trying to acheive
delete data
Hi,
I have two tables.
CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO
CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO
(I fact i created Primary & Foreign keys after inserting data in both of these tables.)
I want to delete data from these two tables.
How do i do that...Any Ideas?Originally posted by naveen_mehta
Hi,
I have two tables.
CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO
CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO
(I fact i created Primary & Foreign keys after inserting data in both of these tables.)
I want to delete data from these two tables.
How do i do that...Any Ideas?
ALTER TABLE ONE NOCHECK CONSTRAINT FK_one_two
ALTER TABLE TWO NOCHECK CONSTRAINT FK_two_one
DELETE ONE
DELETE TWO
ALTER TABLE ONE CHECK CONSTRAINT FK_one_two
ALTER TABLE TWO CHECK CONSTRAINT FK_two_one|||That really works...Thanks a ton...|||You could similarly use the alter statements while inserting data if you do not want to check for constraints
I have two tables.
CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO
CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO
(I fact i created Primary & Foreign keys after inserting data in both of these tables.)
I want to delete data from these two tables.
How do i do that...Any Ideas?Originally posted by naveen_mehta
Hi,
I have two tables.
CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO
CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO
(I fact i created Primary & Foreign keys after inserting data in both of these tables.)
I want to delete data from these two tables.
How do i do that...Any Ideas?
ALTER TABLE ONE NOCHECK CONSTRAINT FK_one_two
ALTER TABLE TWO NOCHECK CONSTRAINT FK_two_one
DELETE ONE
DELETE TWO
ALTER TABLE ONE CHECK CONSTRAINT FK_one_two
ALTER TABLE TWO CHECK CONSTRAINT FK_two_one|||That really works...Thanks a ton...|||You could similarly use the alter statements while inserting data if you do not want to check for constraints
Delete Data
Simple question, what is that I should use to delete data from one table and
keep the table's definition? DROP TABLE deletes everything and I need to
maintain the table's definition.
Thanks a lot.
TSYou can use "truncate table table_name" or "delete table_name". The first on
e
is not a logged operation.
AMB
"TS" wrote:
> Simple question, what is that I should use to delete data from one table a
nd
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TS|||In order to do a truncate table, there must be no foreign keys...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:A95E62C6-A8C1-4590-B71C-DE8726761C9F@.microsoft.com...
> Simple question, what is that I should use to delete data from one table
> and
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TS|||If there are foreign keys pointing to the table, you need to clean those up
first.
If there are foreign keys in the table pointing elsewhere, you need to say
DELETE tablename
Otherwise you can use
TRUNCATE TABLE tablename
(Which also resets the IDENTITY seed if such a column exists.)
"TS" <TS@.discussions.microsoft.com> wrote in message
news:A95E62C6-A8C1-4590-B71C-DE8726761C9F@.microsoft.com...
> Simple question, what is that I should use to delete data from one table
> and
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TSsql
keep the table's definition? DROP TABLE deletes everything and I need to
maintain the table's definition.
Thanks a lot.
TSYou can use "truncate table table_name" or "delete table_name". The first on
e
is not a logged operation.
AMB
"TS" wrote:
> Simple question, what is that I should use to delete data from one table a
nd
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TS|||In order to do a truncate table, there must be no foreign keys...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:A95E62C6-A8C1-4590-B71C-DE8726761C9F@.microsoft.com...
> Simple question, what is that I should use to delete data from one table
> and
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TS|||If there are foreign keys pointing to the table, you need to clean those up
first.
If there are foreign keys in the table pointing elsewhere, you need to say
DELETE tablename
Otherwise you can use
TRUNCATE TABLE tablename
(Which also resets the IDENTITY seed if such a column exists.)
"TS" <TS@.discussions.microsoft.com> wrote in message
news:A95E62C6-A8C1-4590-B71C-DE8726761C9F@.microsoft.com...
> Simple question, what is that I should use to delete data from one table
> and
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TSsql
delete constraint
how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.INSTEAD OF DELETE triggers|||so u mean using triggers instead of delete cascade? i can check if that record exists in other tables using triggers and take necessary action but i want to return relevant message to user if it couldn't be deleted. how can i do that?|||Have a look at RAISERROR|||i was able to manage the following code but couldn't get the message displayed when user tries to delete the record. if i run the stored proc from query analyzer, i get the following error message but not in my application. i have tried catching any exceptions using ex.Message but can't get the error. so how can i get the error displayed when user violates the delete constraint? i have used ExecuteNonQuery command.
|||Try ON DELETE {NO ACTION and ON UPDATE {NO ACTION, this will not allow Deletes and Updates. Hope this helps.
IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR ('Cannot delete this record. Make sure that this record is not used in other tables',9,1)
RETURN
END
Kind regards,
Gift Peddie|||but i want the message to be returned if it couldn't be deleted so that relevant message can be displayed to the user.|||CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON UPDATE NO ACTION,
qty_ordered int)
GO
Run a search in the BOL(books online) for Cascade Delete, the following code is from the BOL it means deletes or updates will fail with an error message. Hope this helps.
Kind regards,
Gift Peddie|||but how can we be sure about the source of error this way? with raiserror as i have done, i can get the message if there are other errors as well. else i don't get any error message. isn't that suupposed to return error message?|||The error from NO ACTION is ANSI SQL from DRI(Declarative Referential Integrity) rules but if you prefer Raise Error you can use it run a search for raise error in the BOL(books online). Hope this helps.
Kind regards,
Gift Peddie|||
I do it this way, for now. Probably better way would be to make Delete function that returns an error message rather then raising new exception. Maybe even make a some kind of class that interprets these error numbers and returns standard error message.
In SqlDataProvider for module
PublicOverridesSub DeleteClientDepartmentsItem(ByVal itemIDAsInteger)
Try
SqlHelper.ExecuteNonQuery(_connectionString, _databaseOwner & _objectQualifier & _
"esr_ClientDepartments_Delete", itemID)
Catch exAs SqlException
If ex.Number = 547Then
ThrowNew Exception("This record cannot be deleted due to its association with other records.")
EndIf
EndTry
EndSub
'in the page:
Try
Dim cdcAsNew ClientDepartmentsController
Dim departmentIdAsInteger =CType(dgDepartments.DataKeys(e.Item.ItemIndex),Integer)
cdc.Delete(departmentId)
BinddgDepartments()
Catch exAs Exception
DotNetNuke.UI.Skins.Skin.AddModuleMessage(Me, ex.Message, Skins.Controls.ModuleMessage.ModuleMessageType.YellowWarning)
EndTry
Delete Column
I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is store in the table below. If the UdateMonth is not null then I want to delete.
hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEnd10. u can use the following statement
delete from tblMarketing where FinancilaPeriodMonth = 'ThisYearMonthEnd10'
I really want to delete a column based on the following example:
Table 1
FinancilaPeriodMonthUdateMonth
ThisYearMonthEnd01Jan
ThisYearMonthEnd02Feb
ThisYearMonthEnd03Mar
ThisYearMonthEnd04Apr
ThisYearMonthEnd05May
ThisYearMonthEnd06Jun
ThisYearMonthEnd07Jul
ThisYearMonthEnd08Aug
ThisYearMonthEnd09Sep
ThisYearMonthEnd10OctOct
ThisYearMonthEnd11Nov
ThisYearMonthEnd12Dec
Actually two of threads were futher explanation of the problem.
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is store in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriodMonthUdateMonth
ThisYearMonthEnd01Jan
ThisYearMonthEnd02Feb
ThisYearMonthEnd03Mar
ThisYearMonthEnd04Apr
ThisYearMonthEnd05May
ThisYearMonthEnd06Jun
ThisYearMonthEnd07Jul
ThisYearMonthEnd08Aug
ThisYearMonthEnd09Sep
ThisYearMonthEnd10OctOct
ThisYearMonthEnd11Nov
ThisYearMonthEnd12Dec
Can anyone help.
ThanksI want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec
Can anyone help.
Thanks|||------------------------
I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec
Can anyone help.
Thanks|||------------------------
I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec
Can anyone help.
Thanks|||A table is nothing more then a collection of columns. If you want to remove a column you do it for the entire table. You cannot just drop a column for one specific row when it contains some data. When you drop a column, it removes that entire column from the table, data or not.
Ronald :cool:.|||
Quote:
Originally Posted by shieldsco
------------------------
I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec
Can anyone help.
Thanks
hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEnd10. u can use the following statement
delete from tblMarketing where FinancilaPeriodMonth = 'ThisYearMonthEnd10'
I hope i understood ur problem correctly.|||
Quote:
Originally Posted by Anu139
hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEnd10. u can use the following statement
delete from tblMarketing where FinancilaPeriodMonth = 'ThisYearMonthEnd10'
I hope i understood ur problem correctly.
I really want to delete a column based on the following example:
Table 1
FinancilaPeriodMonthUdateMonth
ThisYearMonthEnd01Jan
ThisYearMonthEnd02Feb
ThisYearMonthEnd03Mar
ThisYearMonthEnd04Apr
ThisYearMonthEnd05May
ThisYearMonthEnd06Jun
ThisYearMonthEnd07Jul
ThisYearMonthEnd08Aug
ThisYearMonthEnd09Sep
ThisYearMonthEnd10OctOct
ThisYearMonthEnd11Nov
ThisYearMonthEnd12Dec
Table 2
ThisYearMonthEnd01ThisYearMonthEnd02ThisYearMonthEnd10
I want to delete the cloumn ThisYearMonthEnd10 in Table 2 based on the the UdateMonth in table 1.|||Hi shieldsco,
I hope you are getting the help you want. However I would like to request that in future you don't double (or in this case quadruple) post the same question to the same forum.
If you think you question/problem has been overlooked then please post a reply to it yourself so that it gets bumped to the top of the Forum list.
I have now merged your 4 threads on this subject into this single thread.
Regards
Banfa|||
Quote:
Originally Posted by Banfa
Hi shieldsco,
I hope you are getting the help you want. However I would like to request that in future you don't double (or in this case quadruple) post the same question to the same forum.
If you think you question/problem has been overlooked then please post a reply to it yourself so that it gets bumped to the top of the Forum list.
I have now merged your 4 threads on this subject into this single thread.
Regards
Banfa
Actually two of threads were futher explanation of the problem.
DELETE certain rows
I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
table? I'm familiar with the DELETE command but as far as I know it can
only delete one row at a time. How does one delete multiple rows?
Thanks.
RussI found my answer, you can delete multiple rows with the DELETE command.
Russ
"Russ Hromyko" <rhromyko@.verizon.net> wrote in message
news:TWPXi.1645$It.97@.trndny06...
> I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
> table? I'm familiar with the DELETE command but as far as I know it can
> only delete one row at a time. How does one delete multiple rows?
> Thanks.
> Russ
>|||Yes, as you found you can delete rows between 150 and 210 using DELETE.
For example = DELETE FROM Users where [value] >= 150 and [value] <= 210
--
Ekrem Önsoy
"Russ Hromyko" <rhromyko@.verizon.net> wrote in message
news:DGTXi.1664$It.1389@.trndny06...
>I found my answer, you can delete multiple rows with the DELETE command.
> Russ
>
> "Russ Hromyko" <rhromyko@.verizon.net> wrote in message
> news:TWPXi.1645$It.97@.trndny06...
>> I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
>> table? I'm familiar with the DELETE command but as far as I know it can
>> only delete one row at a time. How does one delete multiple rows?
>> Thanks.
>> Russ
>
table? I'm familiar with the DELETE command but as far as I know it can
only delete one row at a time. How does one delete multiple rows?
Thanks.
RussI found my answer, you can delete multiple rows with the DELETE command.
Russ
"Russ Hromyko" <rhromyko@.verizon.net> wrote in message
news:TWPXi.1645$It.97@.trndny06...
> I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
> table? I'm familiar with the DELETE command but as far as I know it can
> only delete one row at a time. How does one delete multiple rows?
> Thanks.
> Russ
>|||Yes, as you found you can delete rows between 150 and 210 using DELETE.
For example = DELETE FROM Users where [value] >= 150 and [value] <= 210
--
Ekrem Önsoy
"Russ Hromyko" <rhromyko@.verizon.net> wrote in message
news:DGTXi.1664$It.1389@.trndny06...
>I found my answer, you can delete multiple rows with the DELETE command.
> Russ
>
> "Russ Hromyko" <rhromyko@.verizon.net> wrote in message
> news:TWPXi.1645$It.97@.trndny06...
>> I'm running SQL 2000, is there a way to delete rows 150 through 210 in a
>> table? I'm familiar with the DELETE command but as far as I know it can
>> only delete one row at a time. How does one delete multiple rows?
>> Thanks.
>> Russ
>
Delete Cascade on cyclic relationship
If I have 3 tables with cyclic relationship like this. Table A has a fk
referencing Table B, table C has a fk referencing table B and table A has a
fk referencing table C.
|--<--fk--|
| |
v |
A--fk-->B--fk-->C
Question is how do I handle "Delete Cascade". How do I write triggers to
handle this?
Thanks,
Tom DOn Sun, 23 Jan 2005 22:35:52 -0800, tom d wrote:
>If I have 3 tables with cyclic relationship like this. Table A has a fk
>referencing Table B, table C has a fk referencing table B and table A has a
>fk referencing table C.
> |--<--fk--|
> | |
> v |
> A--fk-->B--fk-->C
>
>Question is how do I handle "Delete Cascade". How do I write triggers to
>handle this?
>Thanks,
>Tom D
Hi Tom,
I just replieed to your question about the self-referencing table. The
trick for this cyclic relationship is essentially the same. If you are
sure that the nest level won't pass 32, just make a trigger for each of
the tables that will delete from the referencing table all rows
referencing a deleted row, make sure you enable nested and recursive
triggers and don't forget to start each trigger with
IF @.@.ROWCOUNT=0
RETURN
If you think the 32 level nesting limit might cause problems, use the
alternative approach outlined in my other message. This time, you'll have
to use three temp tables and run through a three-step cycle to add rows to
each of these temp tables in turn.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
referencing Table B, table C has a fk referencing table B and table A has a
fk referencing table C.
|--<--fk--|
| |
v |
A--fk-->B--fk-->C
Question is how do I handle "Delete Cascade". How do I write triggers to
handle this?
Thanks,
Tom DOn Sun, 23 Jan 2005 22:35:52 -0800, tom d wrote:
>If I have 3 tables with cyclic relationship like this. Table A has a fk
>referencing Table B, table C has a fk referencing table B and table A has a
>fk referencing table C.
> |--<--fk--|
> | |
> v |
> A--fk-->B--fk-->C
>
>Question is how do I handle "Delete Cascade". How do I write triggers to
>handle this?
>Thanks,
>Tom D
Hi Tom,
I just replieed to your question about the self-referencing table. The
trick for this cyclic relationship is essentially the same. If you are
sure that the nest level won't pass 32, just make a trigger for each of
the tables that will delete from the referencing table all rows
referencing a deleted row, make sure you enable nested and recursive
triggers and don't forget to start each trigger with
IF @.@.ROWCOUNT=0
RETURN
If you think the 32 level nesting limit might cause problems, use the
alternative approach outlined in my other message. This time, you'll have
to use three temp tables and run through a three-step cycle to add rows to
each of these temp tables in turn.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
delete BLOB objects
When I delete rows from a table that contains an NTEXT column, I get a delet
e
capacity of 100 rows per second. I think this is slow. How can I make it
delete faster?
The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246 byte
s
When I populate the same table with BULK INSERT I get performance of
2000-3000 rows per second. Shouldn’t deletion of rows reach about the same
performance as population of the same data?
I have a primary key, id, which I use to select my rows for deletion like
this:
DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
optimal with a single “clustered index delete”.
I have removed all constraints and all indexes on the table to isolate the
problem as much as possible. I'm running in simple recovery mode.
The funny thing is that if I do the following exercise the delete
performance is about 1000-2000 rows per seconds:
Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
id > 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
.. The update, however, does about 50 rows per second.
If I do this:
Step 1: UPDATE mytable SET ntextcolumn = N’-1’ WHERE id < 10000 and id
> 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
…then delete capacity is ca 15.000 rows per second. The update, however,
does about 33 rows per second.
Is the above behavior normal? Does it really take that much work for SQL
Server to remove the blob-object?When you do the bulk Insert you are most likely getting a minimally logged
load which does not log the actual data in the transaction log. It only
marks which extents have been altered in the bulk load. But when you delete
or Update the row it has to log the text data in the transaction log. That
is a lot of data to log all at once. The delete after the update is faster
for two reasons. One the data is already all in cache and you have no text
data to log. Where is your log file located? If it is not on a RAID 1 or
Raid 10 by itself you should think about moving it.
Andrew J. Kelly SQL MVP
"HenrikF" <HenrikF@.discussions.microsoft.com> wrote in message
news:B2A7FD8D-B356-4EBA-B201-B761C0673EF2@.microsoft.com...
> When I delete rows from a table that contains an NTEXT column, I get a
> delete
> capacity of 100 rows per second. I think this is slow. How can I make it
> delete faster?
> The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246
> bytes
> When I populate the same table with BULK INSERT I get performance of
> 2000-3000 rows per second. Shouldn't deletion of rows reach about the same
> performance as population of the same data?
> I have a primary key, id, which I use to select my rows for deletion like
> this:
> DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
> optimal with a single "clustered index delete".
>
> I have removed all constraints and all indexes on the table to isolate the
> problem as much as possible. I'm running in simple recovery mode.
> The funny thing is that if I do the following exercise the delete
> performance is about 1000-2000 rows per seconds:
>
> Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
> id > 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .. The update, however, does about 50 rows per second.
>
> If I do this:
> Step 1: UPDATE mytable SET ntextcolumn = N'-1' WHERE id < 10000 and id >
> 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .then delete capacity is ca 15.000 rows per second. The update, however,
> does about 33 rows per second.
>
> Is the above behavior normal? Does it really take that much work for SQL
> Server to remove the blob-object?
>sql
e
capacity of 100 rows per second. I think this is slow. How can I make it
delete faster?
The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246 byte
s
When I populate the same table with BULK INSERT I get performance of
2000-3000 rows per second. Shouldn’t deletion of rows reach about the same
performance as population of the same data?
I have a primary key, id, which I use to select my rows for deletion like
this:
DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
optimal with a single “clustered index delete”.
I have removed all constraints and all indexes on the table to isolate the
problem as much as possible. I'm running in simple recovery mode.
The funny thing is that if I do the following exercise the delete
performance is about 1000-2000 rows per seconds:
Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
id > 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
.. The update, however, does about 50 rows per second.
If I do this:
Step 1: UPDATE mytable SET ntextcolumn = N’-1’ WHERE id < 10000 and id
> 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
…then delete capacity is ca 15.000 rows per second. The update, however,
does about 33 rows per second.
Is the above behavior normal? Does it really take that much work for SQL
Server to remove the blob-object?When you do the bulk Insert you are most likely getting a minimally logged
load which does not log the actual data in the transaction log. It only
marks which extents have been altered in the bulk load. But when you delete
or Update the row it has to log the text data in the transaction log. That
is a lot of data to log all at once. The delete after the update is faster
for two reasons. One the data is already all in cache and you have no text
data to log. Where is your log file located? If it is not on a RAID 1 or
Raid 10 by itself you should think about moving it.
Andrew J. Kelly SQL MVP
"HenrikF" <HenrikF@.discussions.microsoft.com> wrote in message
news:B2A7FD8D-B356-4EBA-B201-B761C0673EF2@.microsoft.com...
> When I delete rows from a table that contains an NTEXT column, I get a
> delete
> capacity of 100 rows per second. I think this is slow. How can I make it
> delete faster?
> The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246
> bytes
> When I populate the same table with BULK INSERT I get performance of
> 2000-3000 rows per second. Shouldn't deletion of rows reach about the same
> performance as population of the same data?
> I have a primary key, id, which I use to select my rows for deletion like
> this:
> DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
> optimal with a single "clustered index delete".
>
> I have removed all constraints and all indexes on the table to isolate the
> problem as much as possible. I'm running in simple recovery mode.
> The funny thing is that if I do the following exercise the delete
> performance is about 1000-2000 rows per seconds:
>
> Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
> id > 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .. The update, however, does about 50 rows per second.
>
> If I do this:
> Step 1: UPDATE mytable SET ntextcolumn = N'-1' WHERE id < 10000 and id >
> 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .then delete capacity is ca 15.000 rows per second. The update, however,
> does about 33 rows per second.
>
> Is the above behavior normal? Does it really take that much work for SQL
> Server to remove the blob-object?
>sql
Subscribe to:
Comments (Atom)