December 13, 2012 at 1:20 pm
My table structure
CREATE TABLE trefClientShares{
intLMid int NULL,
intMLid int NULL,
intISid int NULL,
intFRid int NULL
}
I need to figure out a query that will delete all rows that don't have at least 2 non-null values.
December 13, 2012 at 1:38 pm
delete from MyTable
where Col1 is null and Col2 is null and Col3 is null
or Col1 is null and Col2 is null and Col4 is null
or Col1 is null and Col3 is null and Col4 is null
or Col2 is null and Col3 is null and Col4 is null;
Seems like that should do it, right?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2012 at 1:43 pm
That did it. I have no idea why I was unable to see the solution without asking. Brain fry. Everything starting to look the same!!! Thanks!
December 13, 2012 at 1:44 pm
You're welcome. Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2012 at 1:51 pm
Here's another possible approach, in case you ever have to do (many) more columns 🙂 :
DELETE FROM dbo.trefClientShares
WHERE
CASE WHEN intLMid IS NULL THEN 1 ELSE 0 END +
CASE WHEN intMLid IS NULL THEN 1 ELSE 0 END +
CASE WHEN intISid IS NULL THEN 1 ELSE 0 END +
CASE WHEN intFRid IS NULL THEN 1 ELSE 0 END >= 3
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 14, 2012 at 8:01 am
CELKO (12/13/2012)
My table structure
Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.
CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);
I need to figure out a query that will delete all rows that don't have at least 2 non-null values.
No, you need to figure out a lot more than that!
DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;
We don't need a key to solve this problem, so no need for irrelevancies just to satisfy ueber-pedants.
Please read one book on SQL Server because IFNULL() does not exist in SQL Server.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 14, 2012 at 8:16 am
CELKO (12/13/2012)
My table structure
Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.
CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);
I need to figure out a query that will delete all rows that don't have at least 2 non-null values.
No, you need to figure out a lot more than that!
DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;
IFNULL is a MySQL item. In SQL Server, you'd use IsNull or Coalesce.
Also, this solution (Joe's Where clause) is completely wrong.
Let's assume none of the columns are null, and all have positive integer values. For example, they all have 10 as their value. 10+10+10+10 = 40. 40 >= 2, so that row would be deleted. But it's not supposed to be. None of the columns are null in that row.
Total failure to understand the requirements, Joe.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2012 at 8:22 am
GSquared (12/14/2012)
CELKO (12/13/2012)
My table structure
Please read just one little book on SQL and RDBMS. Please, just one little book! This is not a table; it has no key; it has to way to ever have a key!! Why did you put the data type as prefix on the column names? That was 1960's BASIC, not SQL! SQL does not use {} like you did. Her is a guess at fixing the disaster.
CREATE TABLE Client_Shares
(share_id CHAR(10) NOT NULL PRIMARY KEY,
lm_id INTEGER,
ml_id INTEGER,
is_id INTEGER,
r_id INTEGER);
I need to figure out a query that will delete all rows that don't have at least 2 non-null values.
No, you need to figure out a lot more than that!
DELETE FROM Client_Shares
WHERE IFNULL(lm_id, 1) + IFNULL(ml_id, 1)
+ IFNULL(is_id, 1) + IFNULL(t_id, 1) >= 2;
IFNULL is a MySQL item. In SQL Server, you'd use IsNull or Coalesce.
Also, this solution (Joe's Where clause) is completely wrong.
Let's assume none of the columns are null, and all have positive integer values. For example, they all have 10 as their value. 10+10+10+10 = 40. 40 >= 2, so that row would be deleted. But it's not supposed to be. None of the columns are null in that row.
Total failure to understand the requirements, Joe.
Good points!
Besides which, I believe my earlier post was the same idea, except coded correctly 🙂 .
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 14, 2012 at 5:11 pm
CELKO (12/14/2012)
.. Total failure.. Besides which, I believe my earlier post was the same idea, except coded correctly 🙂 .
Yeah, it was awful! :crazy:
What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.
DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;
Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 14, 2012 at 5:43 pm
CELKO (12/14/2012)
[ Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!
But it is fast and portable 😀
On a more serious note, that kind of function nesting used to be standard idioms in early SQL. If you can find some articles and books by David Rozenshtein, he did a lot of stuff with it to create the Characteristic function, etc.
Yeah, and we used to use punch cards and write our own bubble sorts too. For me to use something that indecipherable, there'd have to be a noticeable performance diff.
As to "portable", that really is a myth. There are so many custom approaches in each variant of SQL you can never port anything easily.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 17, 2012 at 6:32 am
CELKO (12/14/2012)
.. Total failure.. Besides which, I believe my earlier post was the same idea, except coded correctly 🙂 .
Yeah, it was awful! :crazy:
What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.
DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;
Why overcomplicate it? Why not the solution I posted, that simply uses Where <col> Is Null? Your solution doesn't improve anything on this, makes it impossible for SQL Server to use indexes on the columns, makes it unclear to anyone reading the code what it's supposed to do (you'll need to add a lot of documentation to your version), and seems to be complexity for the sake of "cleverness".
Edit: And, so far as I know, "Where <col> Is Null" is valid in every version of SQL I've ever seen, so your solution isn't even "more portable".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 17, 2012 at 7:34 am
CELKO (12/14/2012)
.. Total failure.. Besides which, I believe my earlier post
Yeah, it was awful! :crazy:
What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.
DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;
Yep, and this one is total failure too (OP wasn't interested in code obfuscation;-))!
Can you understand the simple fact that around 99% of projects/clients/developers/etc. DO NOT NEED PORTABILITY! Especially, as portability of SQL does not exist for now.
If you really want to avoid CASE expression, it can be achieved in much more elegant way:
DELETE t
FROM trefClientShares t
CROSS APPLY (SELECT COUNT(v) nnv
FROM (VALUES (intLMid),(intMLid),(intISid),(intFRid)) v(v)) a
WHERE a.nnv < 2
December 18, 2012 at 7:09 am
CELKO (12/17/2012)
As to "portable", that really is a myth. There are so many custom approaches in each variant of SQL you can never port anything easily.
Then how I have earned a living writing portable SQL for 25+ years? Why does the US Government have FIPS Flaggers? Why did NIST set up the FIPS-127 conformance test suite?
Portable code has to be designed from the start with this goal. Ever read Jerry Weinberg's classic: The Psychology of Computer Programming: Silver Anniversary Edition (ISBN-13: 978-0932633422). Yes, it is so good it gets a Silver anniversary.
Part of the book deals with setting goals and how that affects the code. If you start off like a cowboy coder with "Larry the Cable Guy" philosophy of "Git'er done!", you get code written fast. If you ask for smallest possible code or the fastest execution, you get other kinds of programs.
We know that 80-95% of the total lifetime cost of a system is in maintaining it. DoD, the largest user of software on Earth, found that code that can port from release to release of the same product or to other products saves you tens of billions of dollars.
Some of my consulting work is for VC's who want to know if the applicant's databases can scale up and port (usually to a mainframe, but more and more, we scale down to mobile devices). Sometimes a Venture Capitalist gets a winner! Winners have to grow. The way to grow is to port.
I have killed projects with a bad recommendation. In one case, two start-ups in the same market space, a POS based on a particular chip, needed a decision. One of them had a really neat, flashy interface and used a proprietary language tuned for that chip. We had a lot of these high level, structured assemblers in those days.
The competition was in ANSI Standard C and had a dull interface. They could have lapsed into assembler from the C and gotten a real boost on the particular chip they had, but they stuck to Standard C.
We went with the dull guys. A few months later, the proprietary language and the chip were discontinued. The flashy, non-portable, guys learned one of the laws of ecology. "The better a species is adapted to one environments, the worse it is adapted to all other environments." Or "there are no snakes at the North Pole and no polar bears in the Sahara Desert"
Yes, there's a point where portability is the primary factor. Large bureaucracies often need everything to follow published standards in order to get anything done at all. On the other hand, DoD and other large bureaucracies are more infamous for the projects they waste vast amounts of money and effort on and never finish (in some cases due to overemphasis on obsolete standards) than they are famous for getting things done efficiently or even well.
Another point is that, within its proper environment, a specialized organism is often vastly superior to a generalized one. Rats are highly generalized organisms, capable of surviving in a wide range of environments, while methanopyrus (http://en.wikipedia.org/wiki/Methanopyrus_kandleri) is an extremely specialized one capable of surviving only in a very specific environment. But if you put a rat in a pot of boiling sufuric acid, it won't survive as well as some mehanopyrus would in the same pot.
Keep in mind that some extremophile organisms are thought to be essentially unmodified over about 4 billion years. So, long-term survivability of these extremely specialized organisms isn't really a question.
So your point doesn't actually prove your point, if you get my point. 😀
On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 18, 2012 at 8:08 am
To SELKO:
GSquared (12/18/2012)
...So your point doesn't actually prove your point, if you get my point. 😀
On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.
I do like your point and the way you pointed it out! :hehe:
There is actually one interesting example in IT world too.
There was recently one successful start-up IT company in UK, specialising in the Mobile phone games. Their initial idea was to develop games in portable code, so it could be played on different devices without much changes. They were very successful in this idea and they found venture capitalist to finance them. But after initial success and getting finance, they stopped concentrating on code portability, as to develop the best possible product for particular device (eg. OS) is often impossible (or financially unsustainable) without utilising of the proprietary device (OS) features.
With available financial power and relevant expertise, this company found that it's more appropriate and profitable to develops games for each OS separately using the most appropriate proprietor features of the OS. Yeah, they are not government department, so they cannot allow themselves to waste time and money as they are not founded by people taxes...
And on another hand, does Mr. CELKO, by providing the example of some "vendor" software disappearance in "few months time", implies that SQL Server is going to disappear soon and all SQL Server developers should prepare themselves to write code so it can be ported to DB2? 😉
December 18, 2012 at 8:21 am
Eugene Elutin (12/18/2012)
To SELKO:GSquared (12/18/2012)
...So your point doesn't actually prove your point, if you get my point. 😀
On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.
I do like your point and the way you pointed it out! :hehe:
There is actually one interesting example in IT world too.
There was recently one successful start-up IT company in UK, specialising in the Mobile phone games. Their initial idea was to develop games in portable code, so it could be played on different devices without much changes. They were very successful in this idea and they found venture capitalist to finance them. But after initial success and getting finance, they stopped concentrating on code portability, as to develop the best possible product for particular device (eg. OS) is often impossible (or financially unsustainable) without utilising of the proprietary device (OS) features.
With available financial power and relevant expertise, this company found that it's more appropriate and profitable to develops games for each OS separately using the most appropriate proprietor features of the OS. Yeah, they are not government department, so they cannot allow themselves to waste time and money as they are not founded by people taxes...
And on another hand, does Mr. CELKO, by providing the example of some "vendor" software disappearance in "few months time", implies that SQL Server is going to disappear soon and all SQL Server developers should prepare themselves to write code so it can be ported to DB2? 😉
Not exactly, on the part I added emphasis to.
He's made the point before, more clearly than he did here, that writing "portable code" in SQL, also includes writing code that can easily be ported to the next version of SQL Server.
If you write ANSI/ISO standard SQL, instead of specialized T-SQL extensions, then you can expect that SQL Server 2015 will support your queries, even if you wrote them on SQL Server 2000 originally.
It's another case of being "partially correct". Part of this is that "standards compliant SQL" is actually a moving target, as with other standards, since the standards themselves evolve over time.
Then there's the silliness of some of the standards, compared to what's expected by those who actually use the technology. For example, Truncate wasn't accepted into the standard until 2008. Before that, someone like Joe could go off on you for violating standards if you used Truncate. Since then, it's provable that you actually were complying with the what the standard should have been, you were just ahead of your time.
Innovation depends on violation of accepted practices and standards. But innovation doesn't necessarily imply improvement. Sometimes it does improve things, and sometimes it's just the Edsel (http://en.wikipedia.org/wiki/Edsel).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply