Semi Urgent - ARGH Updateable Query

[JaedenRuiner]

I can do this in like 5 seconds in SQL Server but I can't get the query to even ALLOW me to update records.

Table1 - Columns
Query1 - ColumnDesc

SELECT DISTINCT C.Column
FROM Columns C INNER JOIN (
    SELECT Cols.Column, Count(Cols.DataType)
    FROM (SELECT DISTINCT Columns.Column, Columns.DataType FROM Columns) Cols
    WHERE Cols.DataType = 'char' OR Cols.DataType = 'varchar'
    GROUP BY Cols.Column
    HAVING Count(Cols.DataType) > 1
) C2 ON C.Column = C2.Column;

Simple, straight forward query, that grabs all the rows from table Columns where there are more than one DataType per Column [name], and either one of those datatype strings are 'char' or 'varchar'.

Now I want to UPDATE table Columns to set all of the columns whose "column" value is in the above query, and set all those DataType values to 'VarChar'. Thus I run that UPDATE query, and the above query should come back empty afterwords

in SQL Server I would simply write:

UPDATE Columns
SET DataType = 'varchar'
WHERE EXISTS (
   SELECT 1 FROM ColumnDesc INNER JOIN Columns ON ColumnDesc.COlumn = Columns.Column
)

And this would already have been done.
But Access doesn't like that syntax.
using the designer it created sql like this:

UPDATE Columns INNER JOIN ColumnDesc ON Columns.Column = ColumnDesc.Column
SET Columns.DataType = 'varchar'

But that wasn't an "Updateable" query.

I have to run this on several patterns and right now I'm completely screwed if I can't get this to work.

My end goal is to have:
SELECT DISTINCT Columns.Column FROM Columns
return the same # of rows as
SELECT DISTINCT Columns.Column, Columns.DataType FROM Columns

for each duplication I will have to do different algorithm, but I can't even get one update query to work so I'm currently frustrated (and ready to reaffirm my belief that Access should have been discontinued 5 versions ago).

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[Ranman256]

Did you used the query wizard to build the sql?
THIS will get all fields and syntax correct.