Updating multiple columns in sql server 2016

You can specify the records to modify by applying a filter (WHERE clause) and linking the table to other tables and queries.The updated value can be: UPDATE is useful when you want to change many records or when the records that you want to change are in multiple tables. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the US: In this example, the Contacted field is incremented by one if the record has Called = True.These 3 columns in table A are to be updated based on the values present in table B's corresponding 3 columns, which has the foreign key to primary key in table A.

Fullname,',') s ), parts as ( select Id, case when val Id = 1 then val end as Name, case when val Id = 2 then val end as Surname, case when val Id = 3 then val end as Last from cte ), names as ( select Id, MAX(Name) as Name, MAX(Surname) as Surname, MAX(Last) as Last from parts group by Id ) -- select * from names update Names Table set name = n.

I think that I like your query better since it puts the case statements closer to the columns to be updated. As for the where statement, I should have mentioned that both the #Emplist and #Act Empl tables always have the same records (i.e., the same employee IDs listed).

create table mytable(id int, Full Name varchar(50)) Insert into mytable values(1,'Cleo, Smith'),(2,'single Name'),(3,null) --Option 1 select id, --,parsename(Full Name,4) as Name4 --,parsename(Full Name,3) as Name3, parsename(Full Name,2) as Name ,parsename(Full Name,1) as Sur Name FROM (Select id, replace(Fullname,',','.') Full Name from mytable) t --Option 2 SELECT DISTINCT id --, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS carname1 --, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS carname2 , S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name , S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Sur Name FROM ( SELECT *, CAST (N'') AS xmlname FROM Your Table Name ) -- Define the outer query referencing the CTE name. Sur Name; select * from mytable drop table mytable--Option 2 SELECT DISTINCT id , ISNULL(S.a.value('(/H/r)[4]', 'VARCHAR(100)'),'default Value') AS carname4 , ISNULL(S.a.value('(/H/r)[3]', 'VARCHAR(100)'),'xxxxx') AS carname3 , S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name , S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Sur Name FROM ( SELECT *, CAST (N'--create table Names Table (Id int, Full Name nvarchar(200), Name nvarchar(100), Surname nvarchar(100), Last nvarchar(100)) /* insert into Names Table select 1 , N'Cleo, Smith,james',null,null,null insert into Names Table select 2 , N'Eralper, Yılmaz',null,null,null insert into Names Table select 3 , N'Kodyaz',null,null,null */ select * from Names Table ;with cte as ( select n.

Therefore, it should optimize the query so that it is only accessing the row in Table B once for each row in Table A.

Every time I use this syntax, I get the idea that there is probably a better way. Hire Period FROM #Act Empl INNER JOIN (SELECT Employee ID, CASE WHEN MONTH(e. Employee ID IN (SELECT Employee ID FROM #Emp List) ) AE ON AE. If I were to write the same update query, I would probably opt to restate my IN part as a join and also move the sub-select out like this: update a set Hire Date FY = case when month(e. How to handle replication post SQL Server 2000/2008 migration?

(The #Emplist table was left out in both cases since it wasn't needed.) I'm impressed Oleg, if only I'd learned this long ago...

But in that case, it sounds like I can skip the where statement for employee IDs. Both execution plans look exactly the same, but your code looks much better.

If the record has a zip code that matches the lookup table, its City and State fields are replaced.

UPDATE tbl Address INNER JOIN tbl Zip Codes ON [tbl Address].[Zip Code] = [tbl Zip Codes].[Zip Code] SET [tbl Address].[City] = [tbl Zip Codes].[City], [tbl Address].[State] = [tbl Zip Codes].[State] WHERE ([Country] = 'US') Note: This doesn't work if you're in an ADP since those queries run on SQL Server which doesn't know VBA syntax.

Here is code to build the table and add some sample data. Update By App3Date ) ud ) Last Update Date FROM ##Test Table tt As we can see the first two solutions have more compact code and therefore it is probably easier for developers to use, but what can be said about performance? Below we are creating a new table and adding a lot more test data into our table: --1 SELECT ID, (SELECT MAX(Last Update Date) FROM (VALUES (Update By App1Date),(Update By App2Date),(Update By App3Date)) AS Update Date(Last Update Date)) AS Last Update Date FROM ##Test Table --2 SELECT ID, MAX(Update Date) AS Last Update Date FROM ##Test Table UNPIVOT ( Update Date FOR Date Val IN ( Update By App1Date, Update By App2Date, Update By App3Date ) ) AS u GROUP BY ID, Name --3 SELECT ID, MAX(Update Date) AS Last Update Date FROM ( SELECT ID, Update By App1Date AS Update Date FROM ##Test Table UNION SELECT ID, Update By App2Date AS Update Date FROM ##Test Table UNION SELECT ID, Update By App3Date AS Update Date FROM ##Test Table ) ud GROUP BY ID --4 SELECT ID, ( SELECT MAX(Update Date) AS Last Update Date FROM ( SELECT tt. We can also see that each query used a totally different execution plan.

IF (OBJECT_ID('tempdb..##Test Table') IS NOT NULL) DROP TABLE ##Test Table CREATE TABLE ##Test Table ( ID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(40), Update By App1Date DATETIME, Update By App2Date DATETIME, Update By App3Date DATETIME ) INSERT INTO ##Test Table(Name, Update By App1Date, Update By App2Date, Update By App3Date ) VALUES('ABC', '2015-08-05','2015-08-04', '2015-08-06'), ('New Copmany', '2014-07-05','2012-12-09', '2015-08-14'), ('My Company', '2015-03-05','2015-01-14', '2015-07-26') SELECT * FROM ##Test Table SELECT ID, MAX(Update Date) AS Last Update Date FROM ( SELECT ID, Update By App1Date AS Update Date FROM ##Test Table UNION SELECT ID, Update By App2Date AS Update Date FROM ##Test Table UNION SELECT ID, Update By App3Date AS Update Date FROM ##Test Table ) ud GROUP BY ID SELECT ID, ( SELECT MAX(Update Date) AS Last Update Date FROM ( SELECT tt. We can run these queries separately and see the execution time for the first query is minimal (if the queries run very fast on your server, you can increase the rowcount in your testing table to have a more clear comparison of run times).

You must have an account to comment. Please register or login here!