0

I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table.

To me, the query is strangely written (UPDATE tblTid but SET viewOrderAlla...), but this is what I have to work with. How would I rewrite this query to do the same?

UPDATE tblTid RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr 
SET viewOrderAlla.tblOrder_verkligtid = 0
WHERE viewOrderAlla.tblOrder_verkligtid<>0 AND tblTid.iTidID Is Null 
AND viewOrderAlla.tblOrder_levdatum>=DATEADD(month,-6,GETDATE())

I've tried moving the SET to before the RIGHT JOIN as per some other questions here, but I find no answer that even lets me parse the expression without a syntax error.

The tables are linked into an Access 2013 database, and there, the query runs fine. But not in SQL server.

1

Try something like this:

UPDATE viewOrderAlla
SET viewOrderAlla.tblOrder_verkligtid = 0
FROM tblTid
RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr
WHERE viewOrderAlla.tblOrder_verkligtid <> 0
    AND tblTid.iTidID IS NULL
    AND viewOrderAlla.tblOrder_levdatum >= DATEADD(month, - 6, GETDATE())
1

If you're familiar with writing SELECT Statements, using a CTE to separate the SELECT from the UPDATE can make it easier to get your head around what the query is doing.

;WITH cte AS (
    SELECT
        iewOrderAlla.tblOrder_verkligtid AS [OldValue]
        ,0 AS [NewValue]
    FROM tblTid
    RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr
    WHERE viewOrderAlla.tblOrder_verkligtid <> 0
        AND tblTid.iTidID IS NULL
        AND viewOrderAlla.tblOrder_levdatum >= DATEADD(month, - 6, GETDATE())
)
UPDATE cte
SET [OldValue] = [NewValue]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.