Issue creating consolidated SQL script with multiple ALTER & UPDATES to one table

Question

I am working on a consolidated SQL script that needs to

Add a datetime column named AdjudicatedDateTime to the WarrantyAdjudicationStaging table.
Add a default date of GETDATE() for new entries into the table
Update old records with NULL values of the AdjudicatedDateTime

The statements seem to run fine alone, but I need them to run together.When I attempt to run them together I get the following error:

Msg 207, Level 16, State 1, Line 27
Invalid column name 'AdjudicatedDateTime'

USE NATesterData
ALTER TABLE WarrantyAdjudicationStaging
ADD AdjudicatedDateTime datetime

USE NATesterData
ALTER TABLE WarrantyAdjudicationStaging
ADD CONSTRAINT DF_WarrantyAdjudicationStaging DEFAULT GETDATE() FOR AdjudicatedDateTime

USE NATesterData
UPDATE WarrantyAdjudicationStaging
SET AdjudicatedDateTime = AuditResults.AdjudicatedDateTime
FROM WarrantyAdjudicationStaging was
INNER JOIN
(
    SELECT MAX(dateTimeRun) AdjudicatedDateTime, was.WarrantyAdjudicationGroupID
    FROM WarrantyAdjudicationStaging was
    INNER JOIN (
        SELECT DISTINCT WarrantyAdjudicationGroupID
        FROM WarrantyAdjudicationStaging
        WHERE WarrantyAuditKey IS NULL
    ) exceptions
    ON was.WarrantyAdjudicationGroupID = exceptions.WarrantyAdjudicationGroupID
    INNER JOIN WarrantyUsageAudit wua
    ON was.WarrantyAuditKey= wua.WarrantyAuditKey
    GROUP BY was.WarrantyAdjudicationGroupID
) AuditResults on was.WarrantyAdjudicationGroupID = AuditResults.WarrantyAdjudicationGroupID
WHERE WAS.AdjudicatedDateTime IS NULL
AND WarrantyAuditKey IS NULL

So, what's the problem? What is your question? Why 3 USE statements, when they are define the same database? — Thom A
– Thom A ♦, Commented Jun 22, 2020 at 20:04
When I execute the query I get this "Msg 207, Level 16, State 1, Line 27 Invalid column name 'AdjudicatedDateTime'." — Ace
– Ace, Commented Jun 22, 2020 at 20:11
Put a GO between each block. Remove the unnecessary USE statements. You can combine the first two bocks into one statement... add a column with a default constraint. The syntax is straight-forward. — pmbAustin
– pmbAustin, Commented Jun 22, 2020 at 20:23

Wouter · Accepted Answer · 2020-06-22 20:21:25Z

1

Add a GO after the statements to split them in batches

answered Jun 22, 2020 at 20:21

Wouter

2,9763 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thom A Over a year ago

Note that depending on what is sending the statements GO could cause further errors. GO is not a Transact-SQL Keyword, it's an IDE keyword, used by applications such as SSMS and Azure Data Studio, and CLIs such as sqlcmd and sqlcli.

Collectives™ on Stack Overflow

Issue creating consolidated SQL script with multiple ALTER & UPDATES to one table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related