Tag Archives: SQL Server

MS Access UPDATE with JOIN

Posted on by

I’m forced to use MS Access for some legacy system. I don’t know what Microsoft thought when they made it so that MS Access uses a flavor of SQL that’s pretty different from their main product SQL Server. Did they just think nothing or is there some evil intent? Nothing probably. But anyhow, they did it.

In this post, I describe how to do a UPDATE with JOIN query in MS Access.

I want to replicate the following SQL Server query (in TSQL) in MS Access.

-- MSSQL
UPDATE Sachwert SET Wert = '1', DATE = GETDATE()
    FROM Artikel a
    INNER JOIN Sachwert AS s on a.GUID = s.GUID
    INNER JOIN Sgpos AS sgp on sgp.SACHGRUPPE = s.SACHGRUPPE 
    INNER JOIN Catalog c on c.Year = a.Year 
    WHERE a.Artikel = '104905' AND sgp.SACHGRUPPE = 'V1'

For MS Access you have to do these changes:

  1. Put the name of the table where the update should happen before each name of each column which you want to change and remove the name of the table which is located before the SET. This gives
    UPDATE SET Sachwert.Wert='1', Sachwert.DATE = GETDATE().
  2. Put the SET clause after the JOINs, remove the word FROM but leave the word UPDATE at the beginning.
  3. If you have defined an alias, you must use this alias in the rest of the query. You then cannot longer use the original name. In our case, the SET clause must change to SET s.Wert='1', s.DATE = GETDATE() because of the Sachwert AS s.
  4. If you have N JOINs, you have to add N-1 sets of parentheses. The left parentheses must all be directly after the UPDATE, the right ones one after each JOIN-clause – except the last one.
  5. GETDATE() is called NOW() in Access.

So in the end you get this:

-- MS Access
UPDATE 
       ((Artikel a
          INNER JOIN Sachwert AS s on a.GUID = s.GUID)
          INNER JOIN Sgpos AS sgp on sgp.SACHGRUPPE = s.SACHGRUPPE)
          INNER JOIN Catalog c on c.Year = a.Year 
       SET s.Wert = '1', s.DATE = NOW()
       WHERE a.Artikel = '104905' AND sgp.SACHGRUPPE = 'V1'

SQL Cheat Sheet

Posted on by

This is my personal SQL cheat sheet. I’m using MS SQL Server 2005.
A fine complete SQL tutorial: W3Schools/SQL

Delete Some or All Rows From a Table

-- delete all, keep indexes and columns 
DELETE FROM table_name; 
-- delete some rows, keep indexes and columns
DELETE FROM table_name WHERE ...

Change Values or Update

UPDATE table_name SET column1=value1,column2=value2,... WHERE ...

Update Table Abc from Table Xyz

UPDATE Abc   SET Abc.AccountNumber = x.AccNum
FROM  Abc a
INNER JOIN Xyz x ON a.Artikel = x.Artikel
WHERE ...

Create and Copy a Table or Duplicate Table

-- copies data and schema, doesn't copy indexes
SELECT * INTO new_table_name FROM old_table_name;
-- creates an empty copy; copies schema, doesn't copy data and indexes
SELECT * INTO new_table_name FROM old_table_name WHERE 1 = 0;

Create a Table From the Result of a Complicated Query

This task can be done with the SELECT INTO statement. Consider the INTO-part in the following example. It creates a table called temp in the DB XYZ.

use ABC;
SELECT-part-of-the-query
  INTO XYZ.dbo.temp  
FROM-part-of-the-query
WHERE-part-of-the-query;

Copy a Table or Copy a Part of a Table

-- this copies the complete content, but does not create the table
INSERT INTO target_table SELECT * FROM source_table

-- this copies a part of the rows without creating the table
INSERT INTO target_table SELECT * FROM source_table WHERE ...

-- this copies only a part of the columns and rows
INSERT INTO target_table (col1, col2) 
    SELECT col1, col2 FROM source_table WHERE ...

Both methods here do not work if target_table contains an identity column. In this case, I have to leave the identity column out of the SELECT list. But there are a lot of columns and I don’ want to write down all the names.
There is a solution: you can get the list of the column’s names via SQL.

Select Into / Insert Into Table Variable Result of Query

SELECT ... INTO a table variable is not possible. You first must declare the table variable and then use INSERT INTO. Example:

DECLARE @artikels TABLE ( art nvarchar(10) );
INSERT INTO @artikels SELECT artikel FROM table WHERE ...
-- No parentheses allowed around the select statement.

Get a Comma Separated List of a Table’s Column Names

SELECT ',' + COLUMN_NAME AS [text()]
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'table_name'
    ORDER BY ORDINAL_POSITION
    FOR XML PATH('')

Above code creates a list of the table’s column names with a leading comma and including the identity column. Now let’s assume the identity column is called ID and it is the first column.
Then we have to trim the first four characters to get the select list we want. We can use the STUFF() function for this.
So the following command creates a comma separated list of the column names of table_name, without the first column called ID.

SELECT STUFF(
  (SELECT ',' + COLUMN_NAME AS [text()]
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'table_name'
    ORDER BY ORDINAL_POSITION
    FOR XML PATH('')
  ), 1, 4, '')

I’ve not yet found out how to use this result in the INSERT INTO statement, aside of coyping the result to the clipboard… Still much better than typing 25 vcolumn names manually.

Get an Ordered List of a Table’s Column Names

SELECT COLUMN_NAME c, DATA_TYPE  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'table_name' ORDER BY c

Copy One Row of a Table Into The Same Table, Changing One Value and Dropping One Column

The easiest way to do this without typing out all the column names is with a temporary table.

SELECT * INTO #TempTable          -- # is for temp tables   
    FROM table_name WHERE ...;   
ALTER TABLE #TempTable DROP COLUMN Id;           -- drop a column
UPDATE #TempTable SET SettingsName = 'default';  -- change a value 

INSERT INTO table_name SELECT * FROM #TempTable; -- copy back 
DROP TABLE #TempTable;

LIKE Query Does Not Work

select * from Table where User  like 'Meyer%';

does not show any results. But when I call
select * from Table; I can clearly see the users called Meyer or Meyerbeer.
Solution: The column name User and all other reserved keywords which are used as column or table names must be escaped with square brackets []. This one works:

select * from Table where [User] like 'Meyer%';

The characters with which you can escape column names and the like are different from DB to DB.

JOIN with LIKE Does Not Work

select * from T1 t inner join Z1 z on t.Name LIKE z.Pattern

does not show any results, even though it should.

Reason: Say Pattern is %a, so we want to look for names ending with a.
If Pattern column has a fixed width of 5 characters, a% is expanded to '%a ' in the query. The solution is simple, use RTRIM:

select * from T1 t inner join Z1 z on t.Name LIKE RTRIM(z.Pattern)

COUNT for a complicated Query

Say you have a quite complicated query SELECT .... and you just want to know the count of lines it returns.
Then you usually can do it like this: select COUNT(1) from (SELECT ....) t
Respect the added t at the end! (Could be any other name instead of t.)

But this does not work if your original query contains nameless columns. E.g.
select COUNT(1) from (SELECT id, RTRIM(name), LTRIM(pass) from x) t will fail. Here you have to add a name to every anonymous column of your original query.

select COUNT(1) from (SELECT id, RTRIM(name) a, LTRIM(pass) b from x) t

Combine COUNT and DISTINCT

SELECT COUNT(DISTINCT Column) FROM Table;
-- If you need to use multiple columns with COUNT and DISITNCT, 
-- you need to use such a query:
SELECT COUNT(1) FROM (SELECT DISTINCT Col1, Col2, ... FROM Table) AS iq 
-- Warning: Both above do not work with null values. If you may have 
-- null values in Column and you want to treat all null values as one 
-- distinct thing, you should do this:
SELECT (SELECT COUNT(DISTINCT Col) FROM Table WHERE Col is not null) 
    +  (SELECT SIGN(COUNT(1)) FROM Table WHERE Col is null)

Combine UNION, TOP and DISTINCT

I want to have the TOP 5 values of a UNION of two DISTINCT queries over two tables. This is the solution:

SELECT TOP 5 * FROM (
    SELECT dimid from TDrawing 
        UNION
    SELECT dimid from TDimensions ) AS u
ORDER BY dimid DESC

Explanation: DISTINCT is not needed at all, as UNION already filters out duplicates. AS u is probably needed only for MSSQL.

Combine UNION and MAX

I want to have the biggest value over two tables. This is the solution:

SELECT MAX(d) FROM (SELECT d from t1 UNION SELECT d from t2) AS u
-- AS u  at the end is probably needed only for MSSQL. But there it is.

Select Entries in One Table Which Do Not Occur in Another

Let’s assume there is one unique key column which exists in both tables.

SELECT * FROM Table1 
WHERE key NOT IN 
    (SELECT key FROM Table2);

Drop a Table Only if it Exists

USE database_name;
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'table_name'))
BEGIN
   DROP TABLE table_name;
END

Attention, the solution above will not work for temp tables. For those you need to do something like this:

IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL
BEGIN
    DROP TABLE #Temp
END

Put the Output of EXEC into a Variable

Here I show how to put the output of an EXEC call to a command line or shell command into a SQL variable.

DECLARE @res varchar(1000)
CREATE TABLE #temp (text varchar(1000))

INSERT INTO #temp EXEC master..xp_cmdshell 'cd'
SELECT @res = text FROM #temp WHERE text IS NOT NULL
-- here @res is something like 'C:\WINDOWS\system32'

Find Differences Between Two Tables

SELECT * FROM table1 EXCEPT (SELECT * FROM table2)  
UNION ALL
SELECT * FROM table2 EXCEPT (SELECT * FROM table1)

Dates And a Difference Between Dates

MSSQL Server 2005 can not parse most of the date-time formats that newer systems understand. What it can parse, is a format like YYYYMMDD HH:MM:SS. You can print a date in this format in C# with DateTime.Now.ToString("yyyyMMdd HH:mm:ss")
A difference in minutes between a date-time given manually and a date-time column called StartDate can be calculated like this:

DATEDIFF(minute, '20150826 14:01:35', StartDate)

Getting only entries where column ChangeDate is younger than now minus 60 days (which means, the entry has been changed in the last 60 days) can be done so:

... WHERE DATEDIFF(day, ChangeDate, GETDATE()) < 60

Set Variable as List Which can be used in WHERE IN Statement

Clean version - use a table variable:

DECLARE @kkk TABLE (a varchar(10))
INSERT INTO @kkk VALUES ('100405'); 
INSERT INTO @kkk VALUES ('104580');
SELECT * FROM table WHERE ARTIKEL IN (SELECT a FROM @kkk)

Dirty version - use a string and a reverse LIKE query:

DECLARE @qqq VARCHAR(100)
SET @qqq = '|100405|104580|260140|'
SELECT * FROM table WHERE @qqq LIKE '%|' + RTRIM(ARTIKEL) + '|%'

The RTRIM above is needed when the Artikel column in table has a fixed which is greater than the length of the query parts like '100450'.

Update with Join

We have a query with a JOIN in it and want to update the result of the query. Let's say the query which shows the entries we want to update is this:

SELECT a.Artikel, a.Name, s.Zeichen, s.Position 
FROM Artikel AS a 
INNER JOIN Sachwert AS s on a.Artikel = s.Artikel 
WHERE (a.Name like 'Z%XX%') and (s.Position = '1' )  
ORDER by a.Artikel

And let's say we want to update every s.Zeichen of the result set to Abc. How do we do this? It is easy. We take exactly the FROM, JOIN and WHERE part of the SELECT query and prepend the UPDATE statement.

UPDATE Sachwert  SET Zeichen = 'Abc'
FROM Artikel AS a 
INNER JOIN Sachwert AS s on a.Artikel = s.Artikel 
WHERE (a.Name like 'Z%XX%') and (s.Position = '1' )

SELECT INTO with UNION

Q: I want to fill the result of a query with UNION into a new table, like I can do it with SELECT ... INTO newTable. How do I do this?
A: Just put the INTO newTable before the first FROM statement, exactly where you'd put it if the query had no UNION. Example:

SELECT a, b, c 
    INTO newTable
               FROM X WHERE ...  UNION
SELECT a, b, c FROM Y WHERE ...  UNION 
SELECT a, b, c FROM Z WHERE ...

Cast one Type into Another

-- Syntax for CAST:  
CAST (expression AS data_type [(length)])  

-- Syntax for CONVERT:  
CONVERT (data_type [(length)], expression [, style])  

-- Convert style constants for CONVERT(DATE, '......', style)
--               yyyy-mm-dd  23  -    yymmdd 12  -    yyyymmdd 112 
--  yyyy-mm-ddThh:mi:ss.mmm 126  -  dd.mm.yy  4  -  dd.mm.yyyy 104

-- Even better often is TRY_PARSE:
TRY_PARSE(expression AS decimal(22,10) USING 'en-GB')

More about CONVERT's style parameter.

Grant Execute-Rights for all Stored Procedures to a User

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
ALTER ROLE db_executor ADD MEMBER theUserName

Use result of a SELECT query in another SELECT

SELECT a.Artikel FROM ( SELECT Artikel FROM table WHERE ... ) AS a

Important is the AS a at the end of the subquery in parentheses. Without the AS, the SELECT FROM SELECT doesn't work.

Delete Duplicates

I want to delete all entries from table Octave that are duplicates in relation to the columns Artikel, Pol, Side2 and where Artikel is not null but leave one of the entries. This trick with grouping and MIN(ID) does it well, given that ID is unique.

DELETE FROM Octave WHERE ID NOT IN
  ( SELECT MIN(ID) FROM Octave GROUP BY Artikel, Pol, Side2 )
  AND Artikel is not null

Get the Number of Changes by the Last Statement

SELECT @@ROWCOUNT

Get the Number of Would-Be Changes of an Update Statement

See on stackoverflow.

Order a Table Permanently in the Database

It is not possible to do this reliably for big tables, they say. But it is easy and mostly works:
Just create a clustered index on the order you want. And Voilá - after that, every time you do a select, it will be in that order. CREATE CLUSTERED INDEX new_index on table (col1, col2);