SQL

Oracle SQL: Update table with data from another table

~ Vitalie Ciobanu

Task:

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.

“Must declare the scalar variable” error in Toad for SQL

~ Vitalie Ciobanu

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.

Searched again with Toad word included. Of course, found the issue description quickly on Toad’s forum: https://forums.toadworld.com/t/data-point-error-must-declare-the-scalar-variable/27407/8

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.

Examples of Count, Count IF, Count duplicates in SQL

~ Vitalie Ciobanu

Count:

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

Install SQL Server on a 64 bit Windows Server–missing SQLncli_x64.msi

~ Vitalie Ciobanu

I had to install a copy of SQL Server 2005 on a Windows Server 2003 R2 SP2 x64. During installation I got a strange error saying that SQLncli_x64.msi file is missing. I was pretty sure that I have the same ISO file as I did while installing SQL so many times before and without any errors… Searching the net, the solution from Microsoft forum was the right one.

Before installing SQL Server, you have to download and install Microsoft SQL Server Native Client x64 file (sqlncli_x64.msi) from Microsoft Download Center.

Note: If you’re going to install Analysis Services too, then make sure to download and install Microsoft SQL Server 2005 Management Objects Collection x64 file (SQLServer2005_XMO_x64.msi) also.

Problem installing WSUS on a remote SQL server.

~ Vitalie Ciobanu

Today I again learned a lesson I knew for some time but was hoping that I can forget it. The lesson was about: “Trust anyone. Test for yourself.”

Having to install WSUS on a remote SQL 2005 that had WSUS database installed before, I asked one SQL guy if renaming the old WSUS database is enough to install a new WSUS DB. He said “yeah, sure!”. Ok then, I started installing WSUS.

Needless to say, installation failed:

There is a problem with the windows installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package.

Two hours of troubleshooting permission and connection problems, made me read all WSUS installation log files located in “X:\Users\username\AppData\Local\Temp”. One of the file was 1 KB size so it does not contain much info. Anyway, the file content was:

Changed database context to 'master'.
Msg 1802, Level 16, State 4, Server SQL-SERVER-NAME,  Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5170, Level 16, State 1, Server SQL-SERVER-NAME,  Line 2
Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SUSDB.mdf' because it already exists. Change the file path or the file name, and retry the operation.
Changed database context to 'master'.

One specific line says it all… “Cannot create file … because it already exists”

I guess when doing something, you have to test for yourself everything. Lesson learned. Again.