The Heap™ – Consultancy ©®

7:34 AM

I have 2 tables Tasks and Maps. I need to update the Tasks.SESSIONID column via MAPS.subcategory and MAPS.category. However. there are mapping also for null values so my update doesn't work on the null values :

UPDATE t
SET    t.SESSTIONID = m.SESSTIONID
FROM   Tasks t
       JOIN maps m
            ON  t.Category__c = m.Category__c
            AND t.Sub_Category__c = m.Sub_Category__c

ypercube

@RoyiNamir change the ON condition to how your mapping is

Royi Namir

This is the maps table.

I need to tkae the sessionid via comparing category/subcategory in TASKS table

and to put it in TASKS.SESSIONID

ypercube

ok

Royi Namir

I could use isnull with special value....but it's ugly

ypercube

How do the null map?

Are there nulls in Tasks columns as well?

Royi Namir

7:37 AM

yes

ypercube

in both columns?

Royi Namir

In other words : look at TASKS , if the category is null and subcategory is null , then take the sessionid value from MAPS where maps.cat=null and maps.subcat = null

^

Yes

maps define all (!) mapping

ypercube

and possibly one of the columns is not null and the other null ?

Royi Namir

yes

take the combination from TASKS , and search that combination in MAPS and update the TASKS.sessionID

ypercube

UPDATE t
SET    t.SESSTIONID = m.SESSTIONID
FROM   Tasks t
       JOIN maps m
            ON   (t.Category__c = m.Category__c OR t.Category__c IS NULL AND m.Category__c IS NULL)
            AND (t.Sub_Category__c = m.Sub_Category__c OR t.Sub_Category__c IS NULL AND  m.Sub_Category__c IS NULL)

and pray that you don't have 2 rows with all nulls

Royi Namir

7:41 AM

in tasks ?

ypercube

in maps

Royi Namir

In tasks I do have :

Andriy M

@RoyiNamir You could probably use the technique described in this article by Paul White: sqlblog.com/blogs/paul_white/archive/2011/06/22/…

Royi Namir

ypercube

Ah yeah. The INTERSECT trick is awesome

It would fit your problem perfectly @RoyiNamir

Royi Namir

7:45 AM

Thanks Andriy for the link , will read !

@ypercube I don't understand how it will impact if I have double in maps

ypercube

if you have 2 rows with nulls, which of the two SessionID values will be used for the update you think?

Royi Namir

Right ,so what will happen ?

(I can't test it. it's production env)

ypercube

If yu have 2 rows, say with (null,null,1) and (null,null,2), both will match the condition and the related rows in tasks will be updated twice.

Whatever update is done last, is what you'll see.

Royi Namir

oh :-)

will it actually update it twice ?

ypercube

You can probably not have such issue, if there is unique constraint on maps (Category__c, SubCategory__c).

Andriy M

7:51 AM

I'm assuming @ypercube means if there are two such rows in MAPS. But if (MAPS.category, MAPS.subcategory) pairs are unique (and for the purpose of uniqueness test, NULLs are considered equal), then you shouldn't worry

ypercube

Yes, what AndriyM said. Nulls and UNIQUE have special treatment in SQL-Server, which is good for your case.

Andriy M

Yes, the UNIQUE constraint on those two columns would be a good idea (at least if that matches the intent).

ypercube

@AndriyM I think your answer wouldn't work as it is in MySQL: dba.stackexchange.com/questions/108811/…

Needs rewriting with JOIN. MySQL cannot handle the same table in WHERE and DELETE/UPDATE statements.

Andriy M

@ypercube I mixed that up then. Thought it was the other way round.

Royi Namir

Thank you guys ( as always).

Andriy M

8:04 AM

@ypercube I remember there is this trick with nesting the table one level deeper. Do you know if it's reliable (as in "are there any gotchas about it")?

ypercube

@AndriyM I never used it.

Joins are usually more "performant" ;)

Andriy M

Right :)

Except it should be a semi-join in this case, unless we can be certain that there can be no more than two rows of "almost duplicates" in this case.

Royi Namir

Didnt work !

UPDATE t
SET    t.SESSTIONID = m.SESSTIONID
FROM   Tasks t
       JOIN maps m
            ON   (t.Category__c = m.Category__c OR t.Category__c IS NULL AND m.Category__c IS NULL)
            AND (t.Sub_Category__c = m.Sub_Category__c OR t.Sub_Category__c IS NULL AND  m.Sub_Category__c IS NULL)

ypercube

What does this show? SELECT SessionID FROM Maps WHERE Category__c IS NULL AND Sub_Category__c IS NULL;

Royi Namir

ypercube

8:10 AM

no rows?

Royi Namir

no rows.

but look :

ypercube

Yes, your first image showed at least one row with null-null.

Andriy M

I can see a big difference between the two queries (other than the absence of WHERE)

ypercube

Are you sure this is the same table?

Andriy M

The first one selected from Maps, the second one from RoyiTest.dbo.maps

Royi Namir

8:13 AM

same

changed it to full :

UPDATE t
SET    t.SESSTIONID = m.SESSTIONID
FROM   [RoyiTest].[dbo].Tasks t
       JOIN [RoyiTest].[dbo].maps m
            ON   (t.Category__c = m.Category__c OR t.Category__c IS NULL AND m.Category__c IS NULL)
            AND (t.Sub_Category__c = m.Sub_Category__c OR t.Sub_Category__c IS NULL AND  m.Sub_Category__c IS NULL)

still , same result

Andriy M

Can you repeat @ypercube's query with a fully qualified the table name?

Royi Namir

^ done it

ypercube

Still [maps] is not the same as maps

Andriy M

I mean the SELECT one

ypercube

SELECT SessionID FROM [RoyiTest].[dbo].[maps] WHERE Category__c IS NULL AND Sub_Category__c IS NULL;

Andriy M

8:15 AM

Is it the same server?

Royi Namir

yes

server dstest / DB royitest.

there is no other !

ypercube

You either runing in different dbs

or different schemas

or you have not nulls but the string 'NULL'

Andriy M

@ypercube Makes sense because in other screenshots the NULLs had a yellow background

and in the last one that showed data it was white

ypercube

Yes, the Tasks has NULLs and the Maps has 'NULL's

Andriy M

@ypercube But it should work same as NULLs, right?

ypercube

8:22 AM

@AndriyM No.

Andriy M

Sorry, meant that as a joke.

Anyway, well spotted.

ypercube

oh boy, I thought you were trolling me. Need coffee.

Andriy M

Because we were close to finding another bug :)

Royi Namir

here is the db

drive.google.com/file/d/0B-ZdBmS5_KJwX19nRUZneDQ2SlE/…

ypercube

@RoyiNamir The first image you posted (I guess from production) has NULLs in the Maps table. In your local test db, you have strings with 'NULL' instead.

Andriy M

8:27 AM

SELECT SessionID FROM [RoyiTest].[dbo].[maps] WHERE Category__c = 'NULL' AND Sub_Category__c = 'NULL';

Royi Namir

Andriy M

UPDATE [RoyiTest].[dbo].[maps] SET Category__c = NULL WHERE Category__c = 'NULL';
UPDATE [RoyiTest].[dbo].[maps] SET Sub_Category__c = NULL WHERE Sub_Category__c = 'NULL';

Royi Namir

what about the both cases where both are nulls ?

ps ( in 2 min i will delete the link)

dezso

morning

I think you are the only one who can answer your question. If you write a script for this (which is ready), you should know what you've implemented, right? — dezso 1 min ago

Andriy M

@RoyiNamir Those just update both columns separately, including the row where both are 'NULL'

Royi Namir

8:31 AM

oh i see , you're fixing the mapping

Andriy M

yes

ypercube

In a rather pervert way, we treat placing nulls as fixing today ;)

Royi Namir

ALL good .

Thank you :-) and sorry

dezso

@ypercube somebodies just don't math

Andriy M

8:46 AM

@ypercube Thanks for your edit.

Royi Namir

ps ( it was an exported Db from another company)

no way I would use null as strings

ypercube

@AndriyM I didn't know if you wanted the first query removed or not.

Andriy M

9:02 AM

@ypercube Actually I wanted to stop pretending to be a MySQL expert :)

If I removed the first query, that would be essentially your answer posted under my name.

Andriy M

9:13 AM

Even if I could help you with a SQL query (of which I'm not sure), you would probably still need help integrating the query into your PHP script. However, it looks like you are using some framework which allows you to build database queries in a different way (I'm talking about this fragment: $attendance = DB::table('wys_teacherattendances') ->where('t_amonth', $amonth) ->where('t_ayear', $ayear) ->get(); – that's not SQL). I'm not familiar with that framework. Overall, it seems to me your question needs to be migrated to Stack Overflow. — Andriy M 11 secs ago

What are your thoughts?

ypercube

12:42 PM

@AndriyM You don't reply to them, sir ;)

thank you sir ..im also using this code($attendance = DB::table('wys_teacherattendances') ->where('t_amonth', $amonth) ->where('t_ayear', $ayear) ->get();) to my controller .php .but The output I get is incorrect format.. iam already post this question in stack overflow but no reply. i thought , problem in my sql query? — user71959 3 hours ago

dezso

1:04 PM

@ypercube and they are waiting for you :)

I've added composite indexes on both tables but seen no change in execution time or query plan. Could you elaborate on adding NOT EXISTS? — Tom 42 mins ago

ConcernedOfTunbridgeWells

1:53 PM

@Ypercube, @JackDouglas, @MarkStorey-Smith, @MarkSinkinson, @MartinSmith, @JamesLupolt - How does Friday 28 August sound for a drinkies?

2

ypercube

Hm. not sure.

Friday 21 would be better for me

ConcernedOfTunbridgeWells

@MarkStorey-Smith - are you in town for Fri 28 Aug, or would some other date be better?

@ypercube - 21st works OK for me - let's see how it works for others.

ypercube

@ConcernedOfTunbridgeWells Not that I complain but why the change from Thursdays to Fridays?

ConcernedOfTunbridgeWells

@ypercube Didn't we do Friday last time? I can't remember now.

ypercube

oh yes, I think we did

dezso

2:09 PM

@TomV do you really think that joins without the JOIN syntax are more readable?

(BTW, congrats for making it into the BrentO newletter)

ypercube

@ConcernedOfTunbridgeWells Any day before 21 would be ok, too. Family arrives at 22nd, so the first weeks - at least - I'll be busy ;)

dezso

@Vérace I am wondering if you missed postgres from the examples

22

A: Why should a key be made explicit?

You are obviously suggesting that CONSTRAINTs in a database should be enforced by the application(s) that access that database? There are many reasons why this is a bad (bad, bad...) idea. 1) If you are building a "roll-your-own" constraint "engine" (i.e. within your application code), then you...

(BTW, congrats for making it into the BrentO newletter)

billinkc

(BTW, congrats for making it into the BrentO newsletter)

dezso

2:27 PM

two parrots in the room!

billinkc

Everything is awesome with Princess Yellow Feather

►

Vérace

2:54 PM

@dezso No - I didn't miss PostgreSQL - it's just that Oracle, MS SQL Server and MySQL are significantly more popular - though I have seen encouraging recent signs that PostgreSQL is finally making significant inroads in the MySQL juggernaut. Got a URL for the BrentO newsletter reference? I presume it's Brent Ozar?

dezso

@Vérace us2.campaign-archive1.com/…

Vérace

@dezso You don't have the specific URL? That page has a number of links and no search facility.

swasheck

Why Define Keys?

Why bother explicitly defining keys in your database?

dezso

@Vérace that's the newsletter

and @swasheck is right, as usual

8

Q: Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value is (hopefully) guaranteed to be unique within each row? The uniqueness should still be there ev...

Vérace

@swasheck Thanks - I was searching for my name!

swasheck

3:05 PM

@dezso you stand alone in that perception :)

Vérace

@dezso I'm not getting the @swasheck reference. Is there something wrong with my answer in that thread?

swasheck

i am le confused

Vérace

@swasheck Moi aussi - confondu! (Me too, confused).

swasheck

@Vérace nope. i was attempting to show you where the link to your answer appeared in the brent ozar newsletter

dezso

@Vérace I guess it would be listed if you joined that company

Vérace

3:18 PM

@dezso Thanks for pointing it out - it's nice to have someone of his stature take note of my humble efforts :-). I took your remark about PostgreSQL to heart - edited post to include your favourite database and mine!

Tom V

@dezso huh?

Re the join syntax (I don't seem to have a reply button on the mobile interface)

dezso

3:46 PM

18

A: Index performance on ON versus WHERE

The performance will be the same. The optimizer will recognize this and create the same plan. On the other hand I wouldn't say they are equal. The first form is far more readable and generally expected. For an example using some tables I have at hand you can see the execution plan is exactly th...

Mikael Eriksson

"First form is far more readable" is referencing the first in Q not answer. Right?

ypercube

@MikaelEriksson I'd think so.

Marian

@MikaelEriksson I'd hope so.

Tom V

Yeah I meant in the op

Mikael Eriksson

@AaronBertrand will "make it so"

Tom V

4:00 PM

Surely

swasheck

don't call me shirley

Tom V

4:42 PM

@AaronBertrand I agree with the edit😉

Tom V

6:16 PM

@MikaelEriksson I feel offended you think otherwise

btw @AaronBertrand, i'll be in utrecht next month

& you have a 1 hr train from antwerp to brussels airport

with a transfer unfortunately

swasheck

when i say "full-thickness tear of the ACL and multiple sprains of the other collateral ligaments" you say ... ?

Tom V

go see my dad?

(he's a kine)

swasheck

6:31 PM

that's quite the trip from Denver :)

billinkc

@swasheck Better you than me

swasheck

@billinkc that's quite kind, coming from you

billinkc

I am a font of kindness. Concern for humanity overfloweth

swasheck

dafont.com/kindness.font

Tom V

strange to get more rep while away than when posting

swasheck

6:36 PM

Tom V

didn't even think the post was that good when compared to some othrs

ypercube

7:16 PM

nomination period began
Jul 27 at 20:00
election begins
in 44 minutes

Tom V

campaigns were a bit weak though, especially on the bribery side of things

billinkc

I predict the winner will be a member of The Heap.

5

Tom V

@billinkc if you were billinUK you would even get that quoted

those bookies 'll bet you anything

billinkc

It's my understanding the bookies in Las Vegas will do the same. People gamble on where the pitchers rosin bag falls

Mike Fal

8:04 PM

Votes cast.

Master Database

8:16 PM

1

Q: 2015 Moderator Election Q&A - Questionnaire

In connection with the moderator elections, we are holding a Q&A thread for the candidates. Questions collected from an earlier thread have been compiled into this one, which shall now serve as the space for the candidates to provide their answers. Due to the lack of submission count, we have sel...

discussion featured election

billinkc

Four men enter, one man moderates

4

Marian

voted :)

swasheck

8:45 PM

> MySQL is treated with mild contempt and NoSQL sometimes with open derision (though it is also widely recognised that both have their place). Is this a good thing? If not, how would you act as a moderator to encourage a different attitude in the community?

(though it is also widely recognised that both have their place) please cite your sources

Kermit

Jack Douglas

Aug 30 '13 at 12:56, by Phil

True. NoSQL has existed from the start of CS. It had just never been named and productised before. Plenty of software used the concepts internally

:)

swasheck

but i am talking about mysql

Jack Douglas

FaceBook?

I wouldn't like to try building FaceBook on postgres

Mikael Eriksson

@TomV I did not. But I thought @dezso did so I tried to help clear things a bit by pointing out that your answer could be interpreted to think you prefer join conditions in the where clause.

Jack Douglas

8:54 PM

I'm sure I remember some folk in here from time to time saying that MySQL is the best option in certain niches

the issue with MySQL and NoSQL is when it's used by default or because it's a fad, when another db would do better.

swasheck

@JackDouglas totally agree. it just stinks that so many decision-makers buy into the fad

Jack Douglas

too true

dezso

@swasheck is this acquired by you?

swasheck

@dezso yep.

bluefeet

@swasheck geez sorry

dezso

9:06 PM

kine1 (archaic) plural of cow
kine2 kinescope

@swasheck ugh

swasheck

@bluefeet it's my fault.

but thanks

dezso

did you do something to get it?

bluefeet

@swasheck your fault?

swasheck

@dezso @bluefeet playing in a competitive softball league i slid into home and my cleat caught on the edge of home plate. leg and knee stopped. body didnt.

bluefeet

ugh that sounds painful

swasheck

9:09 PM

actually wasnt as bad as it sounds ... it's just stiff and annoying. probably going to require surgery ... and skiing is in jeopardy. was going to do warrior dash in a few weeks

... that seems off the table now

dezso

@JackDouglas do you think you had to change more than they had with MySQL?

swasheck

@JackDouglas @JackDouglas yeah ... why's that?

Marian

heh, Rolando is having some fun

2

A: Inserting emoji - smartphones in MySQL database with PHP?

I have this sinking feeling inside that you have a character set problem if you are using InnoDB. When you connect to MySQL, run this query select A.variable_name, A.variable_value global_value, B.variable_value session_value from ( select * from information_schema.global_variab...