« first day (3495 days earlier)      last day (1379 days later) » 

McNets
McNets
7:02 AM
Morning
 
 
1 hour later…
John aka hot2use
John aka hot2use
8:13 AM
morning
 
 
3 hours later…
Andriy M
Andriy M
11:18 AM
Morning
 
Royi Namir
Royi Namir
12:13 PM
Hi. Sql Server.

I have a list of keys (nvarchar) where I need to delete records from 2 tables according to these keys.

I can't delete a Dad record until all its childrens are deleted....


simplification :

delete c from child c join dad d on d.id = c.dadId where d.name in( 'x','y','z')

and then

delete dad d where d.name in( 'x','y','z')

but I really hate to write ` in( 'x','y','z')` twice.

I know I can use dynamic SQL . but is it possible to combine those 2 queries into one usinc CTE or something ?
 
Andriy M
Andriy M
You could declare a table variable @ToDelete (dadId int) and use OUTPUT deleted.dadId INTO @ToDelete (dadId) with the first DELETE. Then you would DELETE FROM dad WHERE Id IN (SELECT dadId FROM @ToDelete).
 
Royi Namir
Royi Namir
Hi @AndriyM I'm sorry. I don't see how the overall query will look alike.
oh let me try...
 
Andriy M
Andriy M
DECLARE @ToDelete (dadId int NOT NULL);

DELETE FROM
  c
OUTPUT
  deleted.dadId INTO @ToDelete (dadId)
FROM
  child AS c
  INNER JOIN dad AS d ON c.dadId = d.Id
WHERE
  d.name IN ('x', 'y', 'z')
;

DELETE FROM
  dad
WHERE
  Id IN (SELECT dadId FROM @ToDelete)
;
Something like that
 
Royi Namir
Royi Namir
Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) Jun 12 2020 20:39:00 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
sorry missed table
 
Andriy M
Andriy M
12:30 PM
So did I, my bad
I actually do that mistake very often, don't know why
 
Royi Namir
Royi Namir
Thank you , as always Andriy
 
John aka hot2use
John aka hot2use
12:45 PM
 
Andriy M
Andriy M
No worries @RoyiNamir
@Johnakahot2use The first thing I thought was, "Oh, never thought of that, something new for me to learn." Then I noticed the date, "Hmm, the article is rather old." And then I noticed the name of the blog.
 
 
1 hour later…
John aka hot2use
John aka hot2use
2:11 PM
:-)
Raymond
I just brought it up as I was disussing NoSQL, hierarchical and relational databases with an apprentice today.
Just one of those articles that caught my attention
this one is slightly better : Structure of the Registry (Microsoft | Docs)
> The registry is a hierarchical database that contains data that is critical for the operation of Windows and the applications and services that run on Windows. The data is structured in a tree format. Each node in the tree is called a key. Each key can contain both subkeys and data entries called values. Sometimes, the presence of a key is all the data that an application requires; other times, an application opens a key and uses the values associated with the key.
 
Paul White 9
Paul White 9
Have you considered approaching the CMs you fired/laid off last fall? — Sébastien Renauld yesterday
Brutal
 
 
3 hours later…
jcolebrand
jcolebrand
4:47 PM
okay wtf gives
If I copy and paste it to any editor, it appears to be char(32)
but it isn't getting replaced
CAST(SUBSTRING(REPLACE(REPLACE(CAST(Description AS NVARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CS_AS,' ','()'),')(',''),163,1)AS INT) 
Conversion failed when converting the nvarchar value '
' to data type int.
So clearly it's a CR or an LF, glad I remembered I could do that >.<
I hate developers
 

« first day (3495 days earlier)      last day (1379 days later) »