UPDATE tbl_employee
SET last_name = 'whatever'
WHERE ssn = '123-456-789';
Easy enough, and the same syntax works for all database engines the same.
CREATE TABLE tbl_Employees
( pk int identity(1000,1)
, last_name varchar(100)
, first_name varchar(100)
, dob datetime, SSN varchar(50)
, comments varchar(500) );
CREATE TABLE stage_Employees
( row_id int identity(1,1)
, last_name varchar(100)
, first_name varchar(100)
, dob varchar(100)
, SSN varchar(50)
, comments varchar(500) );
You will notice that the dob field is varchar in the staging table, and not datetime. This is to avoid that the loading from the external source fails with conversion errors (although not relevant here).
INSERT INTO tbl_Employees (last_name, first_name, dob, SSN, comments)
VALUES ( 'Smith', 'Victor'
, convert(datetime, '1980-05-01',120), '123-456-789', null );
INSERT INTO tbl_Employees (last_name, first_name, dob, SSN, comments)
VALUES ( 'Bond', 'James'
, convert(datetime, '1978-01-01', 120), '007-007-007', null );
INSERT INTO tbl_Employees (last_name, first_name, dob, SSN, comments)
VALUES ( 'Brown', 'Alphonse'
, convert(datetime, '1982-12-06', 120), '789-456-123', null );
INSERT INTO stage_Employees (last_name, first_name, dob, SSN, comments)
VALUES ( 'Smith', 'Victor'
, '1980-01-05', '123-456-789', 'dob updated' );
INSERT INTO stage_Employees (last_name, first_name, dob, SSN, comments)
VALUES ( 'Brown', 'Alfonse'
, '1982-06-12', '789-456-123', 'dob and firstname updated' );
It's very important to remember that for this example, we will consider the SSN as the "primary key", so it must never change for an existing record. If you have a "primary key" that can change in the lifetime of the record, get another primary key. In the context of loading data from an external source, you have to get get the actual primary key of the external source also, and store it in your table.
update tbl_employees
set last_name = stage_employees.last_name;
update tbl_employees
set last_name = stage_employees.last_name
where tbl_employees.ssn = stage_employees.ssn;
In MS SQL Server, both will fail with this:
update tbl_employees
set last_name = stage_employees.last_name
, first_name = stage_employees.first_name
from tbl_employees
join stage_employees
on tbl_employees.ssn = stage_employees.ssn
At this point, I will show the same sql, using table aliases, which will not only shorted your code, but make it even more readable:
update t
set last_name = s.last_name
, first_name = s.first_name
from tbl_employees t
join stage_employees s
on t.ssn = s.ssn
With that syntax, the reply from the sql server will be 2 rows updated.
update tbl_employees t, stage_employees s
set t.last_name = s.last_name
, t.last_name = s.last_name
where t.ssn = s.ssn
...and you will get 2 rows updated.
update tbl_employees t
set ( last_name , first_name ) =
( select s.last_name , s.first_name
from stage_employees s
where s.ssn = t.ssn
)
Oracle will promptly return: 3 rows updated! Errr, wait? 3 rows? ROLLBACK, please!
update tbl_employees t
set ( last_name, first_name ) =
( select ( s.last_name, s.first_name )
from stage_employees s
where s.ssn = t.ssn
)
where exists( select null from stage_employees s
where s.ssn = t.ssn )
We simply added a condition to the main query to check if actually there is a record in the other table.
update ( select t.last_name, s.last_name new_last_name
, t.first_name, s.first_name new_first_name
FROM tbl_employees t
JOIN stage_employees s
ON t.ssn = s.ssn
)
set last_name = new_last_name
, first_name = new_first_name
So, if your tables have those primary keys on SSN, then you will now get 2 rows updated.
merge into tbl_employees t
using ( select * from stage_employees ) s
on ( s.ssn = t.ssn )
when matched
then update set t.last_name = s.last_name
when not matched
then insert ( t.ssn, t.last_name) values ( s.ssn, t.last_name )
;
This time, you will get excepted 2 rows merged.
INSERT INTO tbl_employees (ssn, last_name, first_name)
SELECT ssn, last_name, first_name FROM stage_employees
ON DUPLICATE KEY
UPDATE last_name=VALUES(last_name)
, first_name=VALUES(first_name);
Result shall be 2 rows affected.
-- this table will get filled with the SSN used in the update:
declare @updated_ids table( ssn varchar(50) )
-- do the normal update (on rows with exiting join match)
update t
set last_name = s.last_name
-- this line will do the insert of the processed SSN into the above table
output inserted.ssn into @updated_ids
from tbl_employees t
join stage_employees s
on t.ssn = s.ssn
-- now, we can use that temp table data
insert into tbl_employees ( ssn, last_name )
select s.ssn, s.last_name
from stage_employees s
where not exists (select null from @updated_ids t where t.ssn = s.ssn );
MS SQL Server 2005 (and higher) has also another possibility to do this, using CTE (Common Table Expression):
--prepare a inline view with all the data needed for the update
;WITH x as ( SELECT t.ssn, t.last_name, t.first_name
, s.last_name new_last_name, s.first_name new_first_name
From tbl_employees t
Join stage_employees s
On s.ssn = t.ssn )
-- run the update using that inline view
UPDATE x
SET last_name = new_last_name
, first_name = new_first_name
FROM x
I leave it to your evaluation and personal preferences on which option to choose in the database system you work with.
UPDATE tbl_employees
Set last_name = ( select last_name from stage_employees
where tbl_employees.ssn = stage_employees.ssn )
, first_name = ( select first_name from stage_employees
where tbl_employees.ssn = stage_employees.ssn );
It will run, as there is no syntax error; but it has several issues:
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (14)
Author
Commented:the UPDATE / JOIN does not need the destination table in the FROM part, actually...
Open in new window
I could not believe it until I tested it, and it works in MS SQL 2000, MS SQL 2005 and MS SQL 2008
Commented:
Commented:
Commented:
Commented:
thanks
View More