Updating tables in sql from another table world accommodating definition


Suppose you want to update a column with the value 0, if it that column contains negative value.Let us also assume that there are over 2 million row in that column that has a negative value.

Col2 AS _Col2 FROM T1 JOIN T2 ON T1= T2/*Where clause added to exclude rows that are the same in both tables Handles NULL values correctly*/ WHERE EXISTS(SELECT T1. UPDATE suppliers SET supplier_name = (SELECT FROM customers WHERE customers.customer_id = suppliers.supplier_id) WHERE EXISTS (SELECT FROM customers WHERE customers.customer_id = suppliers.supplier_id); UPDATE Table SET Table.col1 = other_table.col1, Table.col2 = other_table.col2 --select Table.col1, other_table.col, Table.col2,other_table.col2, * FROM Table INNER JOIN other_table ON = other_UPDATE from SELECT with INNER JOIN in SQL Database Since there are too many replies of this post, which are most heavily up-voted, I thought I would provide my suggestion here too.

This also has the same limitation as the proprietary This may be a niche reason to perform an update (for example, mainly used in a procedure), or may be obvious to others, but it should also be stated that you can perform an update-select statement without using join (in case the tables you're updating between have no common field).

I usually need to document what I did, so I use the "results to Text" option when I run the rollbacked query and I save both the script and the result of the OUTPUT.

(Of course this is not practical if I changed too many rows) Merge into product P1 using Product_Bak P2 on P1.id=P2when matched then update set p1.[description]=p2.[description],p1.name=P2.

-- the join clause is whatever suits you DECLARE @tbl1 TABLE (id INT, col1 VARCHAR(10), col2 VARCHAR(10)) DECLARE @tbl2 TABLE (id INT, col1 VARCHAR(10), col2 VARCHAR(10)) INSERT @tbl1 SELECT 1, 'a', 'b' UNION SELECT 2, 'b', 'c' INSERT @tbl2 SELECT 1, '1', '2' UNION SELECT 2, '3', '4' UPDATE t SET t.col1 = a.col1 ,t.col2 = a.col2 FROM ( SELECT id, col1, col2 FROM @tbl2) a INNER JOIN @tbl1 t ON = SELECT * FROM @tbl1 SELECT * FROM @tbl2 -- Sample data: --------------------------------------------------------------------------- CREATE TABLE #SOURCE ([ID] INT, [Desc] VARCHAR(10)); CREATE TABLE #DESTINATION ([ID] INT, [Desc] VARCHAR(10)) INSERT INTO #SOURCE VALUES(1,'Desc_1'), (2, 'Desc_2'), (3, 'Desc_3'); INSERT INTO #DESTINATION VALUES(1,'Desc_4'), (2, 'Desc_5'), (3, 'Desc_6'); --------------------------------------------------------------------------- UPDATE #DESTINATION SET #DESTINATION.[Desc] = #SOURCE.[Desc] FROM #SOURCE WHERE #DESTINATION.[ID] = #SOURCE.[ID] AND #Source.[Desc] = 'Desc_2' drop table uno drop table dos create table uno ( uid int, col1 char(1), col2 char(2) ) create table dos ( did int, col1 char(1), col2 char(2), [sql] char(4) ) insert into uno(uid) values (1) insert into uno(uid) values (2) insert into dos values (1,'a','b',null) insert into dos values (2,'c','d','cool') select * from uno select * from dos What I usually do is putting everything in a rollbacked transaction and using the "OUTPUT": in this way I see everything that is about to happen.