How to UPDATE a RowCount using multiple tables and INNER JOIN in SQL Server 2014-CodePudding

I don't know if this can be done is a single statement, but I thought I'd ask.

There are 3 tables involved in a SQL Server 2014 stored procedure:

CREATE TABLE #TempTable 
(
    EntityID int,
    PrefType smallint,
    Col1 int,
    Col2 int
)

Table1
(
    EntityID int,
    PrefType smallint
)

Table2
(
    EntityID int,
    MyRowCount int
)

The #TempTable is used to process other stuff, and now contains just a few rows. I want to use those rows to UPDATE Table2.MyRowCount for the corresponding EntityID using a COUNT(*) from the Table1 WHERE #TempTable.EntityID = Table1.EntityID AND #TempTable.PrefType = Table1.PrefType.

I tried this:

UPDATE Table2 T2 
INNER JOIN (SELECT T1.EntityID,
                   COUNT(*) T1RowCount
            FROM Table1 T1, #TempTable2 TT 
            WHERE T1.EntityID = TT.EntityID 
              AND T1.PrefType = TT.PrefType
            GROUP BY T1.EntityID) AS T1Temp ON T1Temp.EntityID = T2.EntityID 
SET T2.MyRowCount = T1Temp.T1RowCount

But I get a syntax error (squiggly line under T2 in "UPDATE Table2 T2" above) telling me that:

Incorrect Syntax near 'T2'. Expecting SET.

And another squiggly line under INNER and AS with 'Incorrect Syntax' also.

Could this be a problem with using the older version of SQL Server (2014)? or is my SQL wrong/not possible?

Thanks for any help.

CodePudding user response：

As @DaleK already commented - have a look at the official documentation when in doubt! That should be your #1 stop - always.

Reading the docs, you'd see you have to use this SQL statement:

-- UPDATE, then SET
UPDATE Table2
SET MyRowCount = T1Temp.T1RowCount
-- only then FROM and INNER JOIN
FROM Table2 T2
INNER JOIN (SELECT T1.EntityID,
                   COUNT(*) T1RowCount
            FROM Table1 T1, #TempTable2 TT 
            WHERE T1.EntityID = TT.EntityID 
              AND T1.PrefType = TT.PrefType
            GROUP BY T1.EntityID) AS T1Temp ON T1Temp.EntityID = T2.EntityID