Updating a table from a join dating and chat line numbers
To understand this better let’s take a look at below contrived example. The opinions expressed here are my own and not of my employer.For accuracy and official references refer to MSDN, Microsoft Tech Net, Books Online.When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement. Additionally, we want to update the values of Col2 and Col3 only.When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc.If equal value are found in multiple columns from multiple tables, they are returned in the result set.To simplify syntax, T2 is an alias name for Table2, whose rows we want to update based on matching rows with Table1.
So here it is: ' followed by the alias of the table (you don't need to say the table's name there) And (contrary to what some internet randos will tell you) you don't need to add a where clause to stop the update from applying to all rows of the table.
In such case, I will perform join between target table and source table using join on customer ID.
Let's see the syntax of SQL UPDATE query with JOIN statement.
CREATE TABLE table1 (column1 INT, column2 INT, column3 VARCHAR (100)) INSERT INTO table1 (col1, col2, col3) SELECT 1, 11, 'FIRST' UNION ALL SELECT 11,12, 'SECOND' UNION ALL SELECT 21, 13, 'THIRD' UNION ALL SELECT 31, 14, 'FOURTH' CREATE TABLE table2 (column1 INT, column2 INT, column3 VARCHAR (100)) INSERT INTO table2 (col1, col2, col3) SELECT 1, 21, 'TWO-ONE' UNION ALL SELECT 11, 22, 'TWO-TWO' UNION ALL SELECT 21, 23, 'TWO-THREE' UNION ALL SELECT 31, 24, 'TWO-FOUR' Our requirement is that we have table 2 which has two rows where Col 1 is 21 and 31.
We want to update the value from table 2 to table 1 for the rows where Col 1 is 21 and 31.
Also, I find it amusing that if you to do this in My SQL you basically write all the same things in a completely different order.