January 3, 2018 at 2:39 pm
I have a table, where it has status like active, terminated.
I have checked the duplicate records in the table with PNID how many active and how many terminated are there.. I found only one record.
select PNID, count(*) from ActiveStatus group by PNID HAVING count(*) > 1; -- I found only one PNID which has active and terminated record.
But when I search with PNUI
select PNUI, count(*) from ActiveStatus group by PNUI HAVING count(*) > 1; -- I found a lot
I took one id and search the data .. looks like
PNID PNUI status
68765 01844 Active
10002 01844 terminate
11189 01844 terminate
19189 01844 Active
delete from ActiveStatus
where staus in ('Terminate') and PNID in (select PNUI
from activestatus
group by PNUI) having count(*)>1
Is this query correct to delete records?
January 3, 2018 at 3:21 pm
You're asking for advice from the Internet on what records to delete from your database?
Only you can say whether the query is right, but you should ALWAYS TEST by first SELECTing records with the same criteria. I can, however, tell you that this particular query is wrong.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2018 at 8:02 pm
mcfarlandparkway - Wednesday, January 3, 2018 2:39 PM
>> I have a table, where it has status like active, terminated. <<
Where is the DDL for this table? Keys? Constraints? Data types? Did you read the basic forum rules before you posted?
I don’t you understand the basic principle of data modeling. Status is from the Latin word for a “state of being†And it implies that it has a temporal dimension. For example, if I had a properly named table instead of your vague generic data element name, such as “employment_statusâ€, it’s values would depend on the time interval. If they were currently employed then the end of my interval would be a NULL to indicate that it has not completed yet.
Please read this article:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/
>>I have checked the duplicate records [sic] in the table with pn_id how many active and how many terminated are there.. I found only one record [sic].<<
Please learn the difference between a row and a record. This is a fundamental concept in RDBMS. Now it’s time to start guessing at your DDL and correcting it. Even though we can’t see it 🙁
SELECT pn_id, COUNT(*)
FROM Active_Status --- Invalid table name
GROUP BY pn_id HAVING COUNT(*) > 1;
You don’t understand what a table is! The “active status†is not a set of entities or a relationship, but is a value of an attribute inside a table.
-- I found only one pn_id which has active and terminated record [sic].
>> Is this query [sic]] correct to delete records [sic]?<<
This is a statement, not a query.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 3, 2018 at 8:48 pm
Go away Joe.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 4, 2018 at 7:47 am
Joe,
Don't go away but how about a new years resolution to try and be a bit less aggressive towards newbies and those for whom English is possibly not even a second language. You have a wealth on knowledge and a passion for 'proper' database structures acquired through many decades of experience. Some members have inherited bad designs and as junior members may not have the experience, knowledge, gravitas or system credentials to 'fix' the issues as you see them.
A lot of the time the real data cannot be posted either because it is subject to NDA or data security rules like european GDPR protections or the actual domain model is so complex that to post real data would only obfuscate rather than describe the nature of the problem.
January 4, 2018 at 7:49 am
Joe,
Don't go away but how about a new years resolution to try and be a bit less aggressive towards newbies and those for whom English is possibly not even a second language. You have a wealth on knowledge and a passion for 'proper' database structures acquired through many decades of experience. Some members have inherited bad designs and as junior members may not have the experience, knowledge, gravitas or system credentials to 'fix' the issues as you see them.
A lot of the time the real data cannot be posted either because it is subject to NDA or data security rules like european GDPR protections or the actual domain model is so complex that to post real data would only obfuscate rather than describe the nature of the problem.
having said all of that, a member with 4K+ points should know better ...
January 4, 2018 at 1:43 pm
aaron.reese - Thursday, January 4, 2018 7:47 AMJoe,Don't go away but how about a new years resolution to try and be a bit less aggressive towards newbies and those for whom English is possibly not even a second language. You have a wealth on knowledge and a passion for 'proper' database structures acquired through many decades of experience. Some members have inherited bad designs and as junior members may not have the experience, knowledge, gravitas or system credentials to 'fix' the issues as you see them.
A lot of the time the real data cannot be posted either because it is subject to NDA or data security rules like european GDPR protections or the actual domain model is so complex that to post real data would only obfuscate rather than describe the nature of the problem.
Sorry, but I completely disagree with this post. His knowledge is archaic at best and COMPLETELY not applicable or inefficient or both to modern versions of SQL Server at worst. Right or wrong, people just do NOT develop SQL Server database applications the way his Ivory Tower world believes they should be done. And there is the fact that he has behaved the way he does for as long as I have known him (and I will bet long before that), and MANY people have asked him to change over the decades. So he either cannot or will not.
So IMO he has absolutely no replying to questions on SSC.com, or any other SQL Server-focused forum.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 4, 2018 at 5:51 pm
Let's focus on OP's question - Y or N.
I say just GO for it. OP you do have a backup, right? Please do let us know the result.
January 4, 2018 at 5:55 pm
jcelko212 32090 - Wednesday, January 3, 2018 8:02 PMmcfarlandparkway - Wednesday, January 3, 2018 2:39 PM>> I have a table, where it has status like active, terminated. <<
Where is the DDL for this table? Keys? Constraints? Data types? Did you read the basic forum rules before you posted?
I don’t you understand the basic principle of data modeling. Status is from the Latin word for a “state of being†And it implies that it has a temporal dimension. For example, if I had a properly named table instead of your vague generic data element name, such as “employment_statusâ€, it’s values would depend on the time interval. If they were currently employed then the end of my interval would be a NULL to indicate that it has not completed yet.
Please read this article:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/
>>I have checked the duplicate records [sic] in the table with pn_id how many active and how many terminated are there.. I found only one record [sic].<<
Please learn the difference between a row and a record. This is a fundamental concept in RDBMS. Now it’s time to start guessing at your DDL and correcting it. Even though we can’t see it 🙁
SELECT pn_id, COUNT(*)
FROM Active_Status --- Invalid table name
GROUP BY pn_id HAVING COUNT(*) > 1;You don’t understand what a table is! The “active status†is not a set of entities or a relationship, but is a value of an attribute inside a table.
-- I found only one pn_id which has active and terminated record [sic].
>> Is this query [sic]] correct to delete records [sic]?<<
This is a statement, not a query.
This attitude reminds me of my college days. Wait a minute, Professor Celko? Is that really you!? Doh!
January 5, 2018 at 10:58 am
RandomStream - Thursday, January 4, 2018 5:55 PMjcelko212 32090 - Wednesday, January 3, 2018 8:02 PMmcfarlandparkway - Wednesday, January 3, 2018 2:39 PM>> I have a table, where it has status like active, terminated. <<
Where is the DDL for this table? Keys? Constraints? Data types? Did you read the basic forum rules before you posted?
I don’t you understand the basic principle of data modeling. Status is from the Latin word for a “state of being†And it implies that it has a temporal dimension. For example, if I had a properly named table instead of your vague generic data element name, such as “employment_statusâ€, it’s values would depend on the time interval. If they were currently employed then the end of my interval would be a NULL to indicate that it has not completed yet.
Please read this article:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/
>>I have checked the duplicate records [sic] in the table with pn_id how many active and how many terminated are there.. I found only one record [sic].<<
Please learn the difference between a row and a record. This is a fundamental concept in RDBMS. Now it’s time to start guessing at your DDL and correcting it. Even though we can’t see it 🙁
SELECT pn_id, COUNT(*)
FROM Active_Status --- Invalid table name
GROUP BY pn_id HAVING COUNT(*) > 1;You don’t understand what a table is! The “active status†is not a set of entities or a relationship, but is a value of an attribute inside a table.
-- I found only one pn_id which has active and terminated record [sic].
>> Is this query [sic]] correct to delete records [sic]?<<
This is a statement, not a query.
This attitude reminds me of my college days. Wait a minute, Professor Celko? Is that really you!? Doh!
(watching the drama train chug on by)
January 5, 2018 at 11:15 am
RandomStream - Thursday, January 4, 2018 5:51 PMLet's focus on OP's question - Y or N.
I say just GO for it. OP you do have a backup, right? Please do let us know the result.
The first reply by Drew did answer the question:
Only you can say whether the query is right, but you should ALWAYS TEST by first SELECTing records with the same criteria. I can, however, tell you that this particular query is wrong.
I would say absolutely do NOT go for it. That's not the right thing to do - especially when someone who is great with SQL says it's wrong.
Work on getting the query right first. Post the DDL and sample data.
Sue
January 5, 2018 at 12:10 pm
Sue_H - Friday, January 5, 2018 11:15 AMRandomStream - Thursday, January 4, 2018 5:51 PMLet's focus on OP's question - Y or N.
I say just GO for it. OP you do have a backup, right? Please do let us know the result.
The first reply by Drew did answer the question:
Only you can say whether the query is right, but you should ALWAYS TEST by first SELECTing records with the same criteria. I can, however, tell you that this particular query is wrong.
I would say absolutely do NOT go for it. That's not the right thing to do - especially when someone who is great with SQL says it's wrong.
Work on getting the query right first. Post the DDL and sample data.Sue
I was being serious and sarcastic at the same time - bad form but I don't want to apologize. Some people (such as myself) learn faster and better through mistakes. OP's question and approach were wrong on quite a few levels. No, he doesn't deserve to be talked down. But I was being sincere to ask him to try and fail and hopefully, learn from that.
January 6, 2018 at 12:23 pm
Archaic?Did the ISO and ANSI standard, whichI loveto quote, expire or be replaced? The laws of mathematics andrelational algebra, etc. alsochange?Don’t think so. How about basic software engineering? When did thatchangethe fundamental things I beat on in forumsI missed it?
Ifyou want to scream at me, don’t do it on fundamentals. I’m theguy that tells algebra students that “2+2 = 4†And not that youcan get sufficiently large values of two to make it equal to five (isthat your position?).
>>Rightor wrong, people just do NOT develop SQL Server database applicationsthe way his Ivory Tower world believes they should be done. <<
WhenIdid defense contracting, on systems that could kill a hell of a lotof people, we really did worry about more precision, accuracy andcorrectness of our code in weapon systems. Ihave made a living for 35+ years, picking up the crap left by peoplewith your attitude. Sometimes I’m successful. Sometimes there iscrap code beyond repair.
Then,all of a sudden, the commercial world changethe abbreviation “ROIâ€to mean “risk of incarcerationâ€
>>Andthere is the fact that he has behaved the way he does for as long asI have known him (and I will bet long before that), and MANY peoplehave asked him to change over the decades. So he either cannot orwill not. <<
AsI said, I grew up the defense contracting, and correctness matters. Ialso have the attitude when someone posts a forum they ought to atleast read the rules of that forum. We are volunteers, we are notslaves or servants. We are not teachers, trying to supplement whatthey were too lazy to get Some help as opposed to just raw answers,iWhile it isn their university classes.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply