change a query to an update

Question

I have the following query:

SELECT        UsersAccountLink.UserId, Customer.Account.AccountNumber, Web.Customer.Name, Web.Customer.Street, UsersAccountLink.AccountId
FROM            UsersAccountLink INNER JOIN
                         Web.Customer ON UsersAccountLink.AccountId = Web.Customer.AccountId LEFT OUTER JOIN
                         Customer.Account ON UsersAccountLink.AccountId = Customer.Account.AccountId
WHERE        (Customer.Account.AccountNumber IS NULL)
Order by Name

I need to change the select to and update. I need the UsersAccountLink.UserId to be set to an empty guid if Customer.Account.AcountNumber IS NULL.

I doubt I have this right so here is what I came up with:

UPDATE
    UsersAccountLink
SET
    UsersAccountLink.AccountId = '00000000-0000-0000-0000-000000000000'
FROM            
    UsersAccountLink 
INNER JOIN
    Web.Customer ON UsersAccountLink.AccountId = Web.Customer.AccountId 
LEFT OUTER JOIN
    Customer.Account ON UsersAccountLink.AccountId = Customer.Account.AccountId
WHERE        
    (Customer.Account.AccountNumber IS NULL)

I based that off this post How do I UPDATE from a SELECT in SQL Server?

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id

Two questions:

Is there a way to test run a query, such as an update or delete, before actually running it?
Did I convert it to an update correctly?

Thanks!

Test a code without running it? I don't know.. Can't you make a copy of the database for testing purposes and run against it? — JanT, Sep 16 '14 at 14:49
Run without actually updating or deleting records. The database is huge and located on a hosted server. I would take quite a while to pull down and run it locally. That is, if I were given permissions to pull the whole database. :) — ErocM, Sep 16 '14 at 14:51

score 3 · Accepted Answer · answered Sep 16 '14 at 14:54

This is what you can do to test

insert a select statment here to view the record(s) before the insert
BEgin tran
UPDATE
    UsersAccountLink
SET
    UsersAccountLink.AccountId = '00000000-0000-0000-0000-000000000000'
FROM            
    UsersAccountLink 
INNER JOIN
    Web.Customer ON UsersAccountLink.AccountId = Web.Customer.AccountId 
LEFT OUTER JOIN
    Customer.Account ON UsersAccountLink.AccountId = Customer.Account.AccountId
WHERE        
    (Customer.Account.AccountNumber IS NULL)

insert a select statment here to view the record(s) after the insert
Rollback tran

It is still best to do that on dev not prod. In the particular case you have, I would want to to also test what happens if this is run later on a differnt record. Or at least check to make sure you don't have a unique index on accountid.

I also like to do this:

UPDATE
    UsersAccountLink
SET
    UsersAccountLink.AccountId = '00000000-0000-0000-0000-000000000000'
--SELECT * (or you can specify columns you specifically want to see)

FROM            
    UsersAccountLink 
INNER JOIN
    Web.Customer ON UsersAccountLink.AccountId = Web.Customer.AccountId 
LEFT OUTER JOIN
    Customer.Account ON UsersAccountLink.AccountId = Customer.Account.AccountId
WHERE        
    (Customer.Account.AccountNumber IS NULL)

This allow you to easily check the by running the commented select. It aslo makes it easy to change a select to an update by writing the select, then putting the update info on top of it and commenting everything in the select before the FROM statmenet.

You would have to test on your data, but it looks ok to me. However, I am not familar with your database struture or the meaning of your data, so I cannot say if it is correct except that the syntax (the least important part of a query) seems to be fine. — HLGEM, Sep 16 '14 at 15:02

change a query to an update

1 Answers1