If this code answers the question, consider adding adding some text explaining the code in your answer. Or even use table variable like @tbl, "on Perm Table. 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.
This way, you are far more likely to get more upvotes — and help the questioner learn something new.-- 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' I think if you use [_id] on your #SOURCE not [ID] the same as #DESTINATION's, they might let you do JOIN. When I am happy with what I see, i change the ROLLBACK into COMMIT.
I converted my mysql website to PDO recently thinking that I was now somehow safe from injection attacks.
It was only during the process I realised that some of my sql statements were still built using user input. To a complete novice, it's not fully clear that there is a distinction as many experts throw out the comment about using PDO but don't specify t This works by using UPDATE to iterate over the INNER JOIN.
I have a database with account numbers and card numbers.
I match these to a file to update any card numbers to the account number, so that I am only working with account numbers.
Sebastian covers a technique for this in a recent blog post: sqlity.net/en/2867/update-from-select This will tend to work across almost all DBMS which means learn once, execute everywhere. 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. I know this is old, but just wanted to say this one worked for me.
If that is more important to you than performance you might prefer this answer, especially if your update is a one off to correct some data. My server wont allow FROM to be used in an UPDATE statement. 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).Account Number FROM Retrieve Account Number WHERE [Sales_Lead].[dbo].[Sales_Import]. The arguments for using MERGE (including those in the post from linked above) might be compelling, but one thing to consider might be that according to MSDN: ...MERGE statement works best when the two tables have a complex mixture of matching characteristics... Don't update a value with the same value, it generates extra logging and unnecessary overhead.Lead ID FROM Retrieve Account Number WHERE Sales_Import.leadid = Retrieve Account Number. FROM is proprietary therefore, if you cannot use the MERGE statement because you are using SQL 2005 or earlier, this is an ANSI-compliant method of performing updates with a table source in MSSQL. No SELECT * FROM @TB1 UPDATE table1 SET table1.column = 'some_new_val' WHERE table1IN ( SELECT * FROM ( SELECT table1FROM table1 LEFT JOIN table2 ON ( table2.column = table1.column ) WHERE table1.column = 'some_expected_val' AND table12.column IS NULL ) AS Xalias ) UPDATE table1 SET table1.column = 'some_new_val' WHERE table1IN ( SELECT * FROM ( SELECT table1FROM table1 JOIN table2 ON ( table2.column = table1.column ) WHERE table1.column = 'some_expected_val' ) AS Xalias ) UPDATE Table_A SET Table_A. Source: sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/… DECLARE @TB1 TABLE ( No Int , Name NVarchar(50) ,link No int ) DECLARE @TB2 TABLE ( No Int , Name NVarchar(50) ,link No int ) INSERT INTO @TB1 VALUES(1,'changed person data', 0); INSERT INTO @TB1 VALUES(2,'old linked data of person', 1); INSERT INTO @TB2 SELECT * FROM @TB1 WHERE link No = 0 SELECT * FROM @TB1 SELECT * FROM @TB2 UPDATE @TB1 SET Name = T2. No = T1No SELECT * FROM @TB1 UPDATE application SET omts_received_date = ( SELECT date_created FROM application_history WHERE = application_history.application_id AND application_history.application_status_id = 8 ); DECLARE @TB1 TABLE ( No Int , Name NVarchar(50) ) DECLARE @TB2 TABLE ( No Int , Name NVarchar(50) ) INSERT INTO @TB1 VALUES(1,'asdf'); INSERT INTO @TB1 VALUES(2,'awerq'); INSERT INTO @TB2 VALUES(1,';oiup'); INSERT INTO @TB2 VALUES(2,'lkjhj'); SELECT * FROM @TB1 UPDATE @TB1 SET Name =S. UPDATE Table SET Table.col1 = other_table.col1, Table.col2 = other_table.col2 FROM Table INNER JOIN other_table ON = other_WHERE Table.col1 ! = other_table.col2 OR ( other_table.col1 IS NOT NULL AND Table.col1 IS NULL ) OR ( other_table.col2 IS NOT NULL AND Table.col2 IS NULL ) ; WITH CTE AS (SELECT T1. So all the answers involving the FROM clause returned a syntax error. 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.