TSQL – Update Query


Introduction

On a number of occasions, I’ve been asked for examples of stuff that seems really easy when you have been writing T-SQL for some time, but I wrote this quick query this afternoon and it brought memories flooding back to me of when I first started doing this sort of thing many moons ago, so thought I would pop an example on here.

It’s a basic T-SQL query to update a bunch of fields from one database into another. If you are using this sort of thing for Syteline BE EXTREMETLY CAREFUL. I wouldn’t recommend you do any updates unless you fully understand what the fields are, how they are used, and what impact the change will have. In this example they are all UDF (User Defined Fields) that I have inserted so I am confident it will not have any detrimental impacts

The stumbling block the first time I wrote this was the subtleties of the joined in tables and the where condition. Because you are updating the table, you cannot use it’s alias in the field names during the set statement.

There are 2 considerations that could be made to enhance the code and these are :-

1) Put it into a transaction – this allows you to test it by putting a rollback in place on the first run, then a commit when you are happy with it

2) add conditions to the where clause to only update non matching records – this is updating all records whether they match or not!

The Code

use [database_1]

update item

set
description = ui.description
,uf_shortdescription = ui.uf_shortdescription
, uf_itm_lbl_name = ui.uf_itm_lbl_name
, uf_itm_colour = ui.uf_itm_colour
, uf_x_type = ui.uf_x_type
, uf_x_prodname = ui.uf_x_prodname
, uf_item_type = ui.uf_item_type
, uf_itm_otherinfo = ui.uf_itm_otherinfo
, uf_itm_lbl_def_type = ui.uf_itm_lbl_def_type
, uf_opr_indicator = ui.uf_opr_indicator
, uf_itm_lbl_description = ui.uf_itm_lbl_description
, uf_itm_lbl_compat = ui.uf_itm_lbl_compat
, uf_x_manu = ui.uf_x_manu
, uf_x_compat = ui.uf_x_compat
, uf_itm_msds_file = ui.uf_itm_msds_file
, uf_length = ui.uf_length
, uf_width = ui.uf_width
, uf_depth = ui.uf_depth
from [database_1].dbo.item as ii
left join [database_2].dbo.item as ui on ui.item = ii.item

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.