SQL Server SQL Update/Merge - how to get update statements that make source table unnecessary

jexiocij  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(114)

I have a table Links with fields code (string) and link (string). Some of the links are NULL, so I need to update those values based a correct full mapping in excel (sheet has the same fields). Now, this should be easy, and it is on my local machine, because I can just import the excel sheet into SQL Server. But, in the production servers, I am not able/allowed to add to the existing tables, only to run queries. I have been requested to get SQL to produce a query that would do the same job as the update/merge I am describing--without adding the source table.

Wondering if there is any setting or command that would allow me to access the end-product updates (containing the string form of code and link) that run when I run the update query on my local machine with the other table. Using SQL Server/SSMS 2019.
| code | link |
| ------------ | ------------ |
| PM.23.fd | http://... |
| PM.25.df | NULL |

codelink
PM.23.fdhttp://...
PM.25.dfhttp://...
zujrkrfu

zujrkrfu1#

You can create a "pseudotable" in form of values('pm.25.df', 'http://xyz'),('other', 'http://abc') and then ask the guy to do a merge on the pseudo table in form of:

-- Create testdata
select *
into #sometable
from 
(
    VALUES  (N'PM.23.fd', cast(N'http://dontchange.com' as nvarchar(255)))
    ,   (N'PM.25.df', NULL)
) t (code,link)

-- the script

;merge #sometable as target
using (
    select * from (
        values  ('pm.25.df', 'http://stackoverflow.com') -- here you generate your changes
        ,   ('pm.xyz', 'http://example.com')
        ,   (N'PM.23.fd', N'http://change.com')
        ) x(code, link)
    ) as source
    ON source.code = target.code
when matched and target.link IS NULL then update set link = source.link
when not matched then insert (code, link) values(source.code, source.link)
;

I use merge because you mentioned it, but if you only interested in update, you can just change the query:

UPDATE  target
SET link = source.link
FROM    #sometable target
INNER JOIN (
        VALUES  ('pm.25.df', 'http://stackoverflow.com')
        ,   ('pm.xyz', 'http://example.com')
        ,   (N'PM.23.fd', N'http://change.com')
    )  source (code, link)
    ON  source.code = target.code
WHERE   target.link IS NULL

This only updates codes where link is null.

If you have more than 1000 rows, then values will not work, then you generate a temp table that you insert into

相关问题