Let’s suppose you have Table1 with a million rows and you just altered the table by adding a new column. This new column is empty (NULL) in Table1 but you want to update it with the values that are in a column in Table2.
Solution:
First, do not ruin your day, create a backup table with the same structure and data as TABLE1 by running below query:
CREATE TABLE TABLE1_BKP AS SELECT * FROM TABLE1;
Then test the update query by running below query:
UPDATE TABLE1_BKP A SET
A.COLUMN_TO_UPDATE = (SELECT B.COLUMN_WITH_DATA FROM TABLE2 B WHERE B.COLUMN_WITH_DATA IS NOT NULL AND B.ID = A.ID)
WHERE A.ID IN (SELECT B.ID FROM TABLE2 B WHERE B.ID = A.ID);
If the update went well, run the same query again but replace TABLE1_BKP with TABLE1.
It goes without saying that any UPDATE and DELETE queries must have a WHERE clause to limit the result as much as possible, in case something unexpected happens. And that’s why you need a backup table and test on it first.
Yes, at least for now, there are 2 different PowerShell apps out there and both can run on the same computer. I envision Microsoft will slowly remove Windows PowerShell from shipping with Windows by default in the future. It may be replaced by PowerShell or by nothing, leaving it to user’s discretion whether she/he wants to install it manually or not.
There are some differences, modules, cmdlets changes between the two apps. There is a pretty good/complete article that details all the changes, differences etc. Check it out here:
Started learning how to work with variables in T-SQL. Doing first basic exercise to declare today’s date and then print it, failed mizerably.
I was using Toad for SQL because this is my day to day tool. Tried several other examples, searched online for this error… looks like “it works”, but it doesn’t for me?!
Opened Microsoft SQL Management Studio, pasted same example, hit F5. Boom, worked! Ok, then it’s something related to Toad for SQL.
This is not a bug and that is why things are not changing. This is original behavior from Toad for Oracle.
There are different execution needs depending on what you are doing.
When you have a script you might want to only execute the SQL you are working on. For this you use F9 and we figure out what the current statement is based on your cursor and only execute that.
Or when you have a script you might want to execute all in the script from top down. For this you use F5.
There is a third option which is to execute from the current statement to the end. For this there is a menu action but no hot key.
In the case of a SQL that contains a script variable you can’t execute the current statement as the variable must be declared in block of code that is being sent to the server. So in this case F5 is the only option.
Someone asked me the other day below thing: “On the same column I want a Range of dates not Duration between two dates So I have a Creation date and fulfillment date I need to come up with a column of weekly Range like for example 10/21 – 10/27 10/28 – 11/01 11/02 – 11/09 Something like this.”
The solution can be pretty simple and mostly done through Power Query’s GUI.
Add a date range first using either way you want.
Then under Add Column – Date, you can easy add “Start of Week” and “End of Week”.
Note that if your week starts on Sunday, you don’t need to provide the first day of the week. I need my week to start on Monday so I added 1, instead of 0 or nothing “Date.EndOfWeek([Date],1)“.
The only thing to do now is to merge these two columns and put a separator of your choice. This will give you a column with Week’s start and end dates in the long format.
If you want month and date (or only date) you need to extract month and then date separately from both your Start and End of Week columns. Then merge month and date from Start of the week and again from End of the week.
At this moment, you only need to merge these 2 last columns, remove all unneeded columns and that’s it.
Here’s the entire query I ended up with.
let
Query1 = let
Source = #date(2019,1,1),
Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
#"Converted to Table",
#"Changed Type" = Table.TransformColumnTypes(Query1,{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Descending}}),
#"Inserted Start of Week" = Table.AddColumn(#"Sorted Rows", "Start of Week", each Date.StartOfWeek([Date],1), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],1), type date),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted End of Week", "Long range", each Text.Combine({Text.From([Start of Week], "en-US"), Text.From([End of Week], "en-US")}, " - "), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Merged Column", "Month", each Date.Month([Start of Week]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Start of Week]), Int64.Type),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Start"),
#"Inserted Month1" = Table.AddColumn(#"Merged Columns", "Month", each Date.Month([End of Week]), Int64.Type),
#"Inserted Day1" = Table.AddColumn(#"Inserted Month1", "Day", each Date.Day([End of Week]), Int64.Type),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day1", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"End"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Start", "End"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Short range"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns2",{"Start of Week", "End of Week"})
in
#"Removed Columns"
SELECT [ProductModel], COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel]
ORDER BY CNT DESC
Count “if”:
SELECT [ProductModel], [Name],COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
WHERE [ProductModel] LIKE '%Frame' AND [Name] LIKE '%52' --here's the IF statement
GROUP BY [ProductModel], [Name]
ORDER BY CNT DESC
Count duplicates:
SELECT [ProductModel], COUNT(1) CNT -- select a field you want to search for duplicates in
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel] HAVING COUNT(1) > 1 -- if count is greater than 1, then it is a duplicate by that field
ORDER BY CNT DESC
Group count fields a tabular form:
SELECT [ProductModel], [Name], COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel], [Name]
ORDER BY CNT DESC
Ok, I don’t want to make it a long post, so here’s the thing: one of the Data Profiling feature is “Column distribution”.
Column Distribution allows you to get a sense for the overall distribution of values within a column in your data previews, including the count of distinct values (total number of different values found in a given column) and unique values (total number of values that only appear once in a given column).
So, distinct and/or unique. For anyone else these 2 words may seem similar, but for Data Guys like us, these are 2 different things (just like empty and null 🙂 ). Still confused? Here’s a very simple example to clear things up:
Let’s say we have a small company with 10 employees using 10 laptops from different Manufacturers: HP, Dell, Apple and Lenovo. Distinct: We have laptops from 4 different Manufacturers (HP, Dell, Apple and Lenovo) aka “total number of different values”, regardless of how many of each we have. Unique: We have only 2 laptops that nobody else have in our company (one from Apple and another one from Lenovo) aka “total number of values that only appear once”.
Thinking legacy, here’s the same thing in Excel, using countif or countifs.
At this moment, you only need to merge these 2 last columns, remove all unneeded columns and that’s it.
