SQL Update

Examples of SQL UPDATE

1.
What happens if you remove the WHERE clause from SQL Update query:

UPDATE Emp
SET Email = 'new_email@gmail.com'

The answer is that all Email entries in the Emp table will be changed to new_email@gmail.com.

2.
Every occurrence of 'zz' in the Lastname column is updated to 'xx':

Update Employee
Set Lastname = Replace(Lastname, 'zz', 'xx')

3.
Using A SQL JOIN In A SQL UPDATE Statement
SQL JOIN In A SQL UPDATE Statement

4.
Using CASE Statements In A SQL UPDATE Query
CASE Statements in SQL UPDATE

5.
SQL update from one Table to another based on a ID match
StackOverflow

I have a database with Account Numbers and card Numbers that I match to a file to update any card numbers to account number so I only work with account numbers. I created a view linking the Table to the Account/Card db to return the Table ID and the related Account number. Now I need to update those records where the ID matches with the Account Number.

Sales_Import where the account number field needs to be updated.

LeadID AccountNumber

147 5807811235
150 5807811326
185 7006100100007267039

RetrieveAccountNumber where I need to update from

LeadID AccountNumber

147 7006100100007266957
150 7006100100007267039

6.
Oracle: Update table with multiple columns from another table
Asktom

7.
Update multiple columns in SQL with bound multi-part identifier
StackOverflow

Multiple Choice Questions on SQL Insert and Update

Multiple Choice Questions on SQL Insert and Update

1) Which one is correct syntax for Insert Statement?

a) Insert table_name Columns(Col1, Col2,Col3);
b) Insert into table_name (Col1, Col2,Col3) VALUES (Val1,Val2,Val3);
c) Insert Columns(Col1, Col2,Col3) VALUE (Val1, Val2,Val3) Into table_name;
d) None of the above

2) Can the sequence & number of columns and values be different in an Insert statement?

a) True
b) False

3) How is Column wise insertion of data different from simply passing values to a table?

a) Column wise data leads in populating data on optional basis i.e. whether user wanted to insert data in a column or not.
b) We can’t pass value to a table without mentioning column names in an insert statement.
c) Passing values to a table without column names is always safe.
d) None of the above.

4) Update statement is used to insert values in a table and therefore it is a replacement of insert statement, is it correct?

a) True
b) False

5) Which one is correct syntax for Update Statement?

a) Update Table table_name Columns(Col1, Col2,Col3);
b) Update into table_name (Col1, Col2,Col3) VALUES (Val1,Val2,Val3);
c) Update table_name Set Col_name=Value;
d) None of the above

6) Is mentioning column name corresponding to its value mandatory in Update Statement?

a) True
b) False

7) What will be the consequence of omitting ‘Where’ clause in Update Statement?

a) No effect on the query as well as on table.
b) All records present in the table will be updated
c) Only one record will be updated
d) None of the above

8) In any case, can update statement be used in place of insert statement?

a) Yes, if record exists with Identity value.
b) Yes, in every case
c) No, it is not possible at all.
d) None of the above.

9) Can insert be used in place of update?

a) True
b) False
c) It is replacement of update
d) None of the above

10) Can we use ‘where’ clause in an Insert statement?

a) True
b) False


Answers

1) b 2) b, 3) a, 4) b, 5) c, 6) a, 7) b, 8) a, 9) b, 10) b