Updating multiple rows in sql server
This will save Pinal’s time for real )))I only wonder, why you’re not using INSERT VALUES instead of INSERT SELECT UNION ALL – you can really make use of this tiny liitle feature called “Table Value Constructor” and define virtual table expressions on the basis if VALUES.
Anyway, SELECT UNION ALL still works out for anything below SQL Server 2008. Just little note…I prefer to use table name “t1” instead “Table1” in “UPDATE” statement: UPDATE t1 — instead Table1 SET Col2 = t2.
I was change the statement like below, and got the same result, my question is what’s different in these two statements ?
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.
I resolved this by providing the alias name after update query:: UPDATE Table1 SET Col2 = t2.
You can additionally use MERGE statement to do the same as well, however I personally prefer this method. This works for existing tables, whilst Select Into does not. But you should at least mention the ‘new’ merge statement, here’s a good link for you: ; DNice post, Pinal, as always. Col1 IN (21, 31)Reason: Some time Table1 joined to itself by some criteria and “update t1” resolves ambiguous names Hi Folks My Question: I need to update different columns in different table. I have specific character that needs to be replaced with ”In each table I have to update a specific column. Approach: I thought of creating arrays for tab n col Check the code below n help me with this CREATE Or REPLACE TYPE TABARRAY AS VARRAY(3) OF VARCHAR2(10); CREATE Or REPLACE TYPE COLARRAY AS VARRAY(3) OF VARCHAR2(10); CREATE Or REPLACE TYPE ILLARRAY AS VARRAY(3) OF VARCHAR2(10); DECLARE type TABARRAY IS VARRAY(5) OF VARCHAR2(10); TABNAMES TABARRAY; type COLARRAY IS VARRAY(5) OF VARCHAR2(10); COLNAMES COLARRAY; type ILLARRAY IS VARRAY(5) OF VARCHAR2(10); ILLNAMES ILLARRAY; TOTAL INTEGER; BEGIN TABNAMES :=TABARRAY(‘siebel. S_SRV_REQ’); COLNAMES :=COLARRAY(‘X_SPECIAL_SERVICE_CD’,’X_SGSTNS_PREV’); ILLNAMES :=ILLARRAY(‘HE’,’EA’); TOTAL :=TABNAMES. COUNT; FOR I IN TOTAL LOOP FOR J IN ILLTOTAL LOOP UPDATE TABNAMES(I) SET COLNAMES(I)=REPLACE(COLNAMES(I), ILLNAMES(J),”); END LOOP; END LOOP; END; How can i update different Table Column ???? Col2, the server code knows this is “fullyqualifiedpath”Table2 AS t2 and similar for “t1” Is this really an alias or is it something else and how are the name t1 and t2 derived? I was change the statement like below, and got the same result, my question is how different in these two statements ?
Let us clean up the clause by dropping the tables which we have created. For rookies, of course :) I recommend to everybody to read Izik Ben-Gan’s “T-SQL Fundamentals” to know such a basic stuff like JOINs in DML, MERGE, table expressions, subqueries, isolation lvls and blocking, and algebra fundamentals of SQL-language.
This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31.
In recent times I have seen a developer writing a cursor to update a table. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31.