asked
Need help writing single SQL statement to update multiple fields in one table using contents of two other tables based on unique ID field
Suppose we have the following 3 tables: Create table OUTPUT(ID,FirstName, LastName); Create unique index outputpk on OUTPUT(ID); Create table INPUT1(ID, FirstName); Create unique index input1pk on INPUT1 (ID); Create table INPUT2(ID, LastName); Create unique index input2pk on INPUT2 (ID); Give me a SINGLE SQL statement that will update OUTPUT with the contents of INPUT1 and INPUT2 based on ID.
First off, I'd suggest you don't use OUTPUT as a table name, as that's a reserved word in SQL Server, and might cause confusion if you use DDL with an OUTPUT clause. Anyway, given the table names you have chosen, this will probably work. You might want to add some WHERE conditions to the CTE. --ddl Create table OUTPUT(ID int primary key,FirstName varchar(10), LastName varchar(10)); Create table INPUT1(ID int primary key, FirstName varchar(10)); Create table INPUT2(ID int primary key, LastName varchar(10)); --initiate tables insert output (id) values (1), (2); insert input1 (id,firstname) values (1,'magnus'), (2,'john'); insert input2 (id,lastname) values (1,'ahlkvist'), (2,'doe'); --update using CTE with cte as ( select
o.id as oid, o.firstname as ofirstname, o.lastname as olastname, i1.firstname as i1firstname, i2.lastname as i2lastname from output o inner join input1 i1 on
o.id=
i1.id inner join input2 i2 on
o.id=
i2.id ) update cte set ofirstname=i1firstname, olastname=i2lastname --check results select * from output
15 People are following this question.
