SQLTeam.com | Weblogs | Forums

Converting Access DISTINCTROW to TSQL

SQL Server Administration
#1

Hi all,
I've been tasked with converting this Access update query to TSQL. I' not familiar with Access' "DISTINCTROW". Thanks.

UPDATE DISTINCTROW dbo_tblTransactions SET dbo_tblTransactions.curTotalAP = 150, dbo_tblTransactions.curTotalAR = 150, dbo_tblTransactions.curCarrierFSC = 40, dbo_tblTransactions.curCustomerFSC = 40, dbo_tblTransactions.intAlteredBy = 2106, dbo_tblTransactions.dteAlteredOn = Now()

WHERE (((dbo_tblTransactions.curCarrierFSC) Is Null Or (dbo_tblTransactions.curCarrierFSC)=0) AND ((dbo_tblTransactions.intMileage)>0) AND ((dbo_tblTransactions.intCustomer)=312172) AND ((dbo_tblTransactions.intCarrier)=623 Or (dbo_tblTransactions.intCarrier)=822) AND ((dbo_tblTransactions.dteFreightBillRecd) Is Null));

#2

https://support.microsoft.com/en-us/office/all-distinct-distinctrow-top-predicates-24f2a47d-a803-4c7c-8e81-756fe298ce57

Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:

SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;
If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.

DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

1 Like
#3

Thanks yosiaz. In my case, there is no JOIN so I'm not sure why the code is written as DISTINCTROW.

#4

As this query is based on only 1 table, the DISTINCTROW would be ignored anyway. So this is a non- issue. No need to write anything for that in the TSQL equivalent.

1 Like
#5

Make sure to test it. One against access the other again sql