sql-update interview questions
Top sql-update frequently asked interview questions
I have a table with the following columns in a MySQL database
[id, url]
And the urls are like:
http://domain1.com/images/img1.jpg
I want to update all the urls to another domain
http://domain2.com/otherfolder/img1.jpg
keeping the name of the file as is.
What's the query must I run?
Source: (StackOverflow)
What is the correct SQL syntax to insert a value with an apostrophe in it?
Insert into Person
(First, Last)
Values
'Joe',
'O'Brien'
I keep getting an error as I think the apostrophe after the O is the ending tag for the value.
Source: (StackOverflow)
I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL.
Source: (StackOverflow)
I am wondering if this is possible at all. I want to update column x if a condition is true, otherwise column y would be updated
UPDATE table SET
(CASE (CONDITION) WHEN TRUE THEN columnx
ELSE columny
END)
= 25
I have searched all over, tried out some things and am unable to find a solution. I think it's not possible, but I thought I would ask here and see if anyone has done it before. Thanks in advance.
Source: (StackOverflow)
I am using mysql and need to update a column with a null value. I have tried this many different ways and the best I have gotten is an empty string.
Is there a special syntax to do this?
Source: (StackOverflow)
I have a function that updates three tables, but I use three queries to perform this. I wish to use a more convenient approach for good practice.
How can I update multiple tables in MySQL with a single query?
Source: (StackOverflow)
In SQL Server, it's possible to insert into a table using a SELECT
statement:
INSERT INTO Table (col, col2, col3)
SELECT col, col2, col3 FROM other_table WHERE sql = 'cool'
Is it also possible to update via a SELECT
? I have a temporary table containing the values, and would like to update another table using those values. Perhaps something like this:
UPDATE Table SET col1, col2
SELECT col1, col2 FROM other_table WHERE sql = 'cool'
WHERE Table.id = other_table.id
Source: (StackOverflow)
Here's the table
Users
UserId
UserName
Password
EmailAddress
and the code..
public void ChangePassword(int userId, string password){
//code to update the password..
}
Source: (StackOverflow)
Is there a way to update multiple columns in SQL server the same way an insert statement is used?
Something like:
Update table1 set (a,b,c,d,e,f,g,h,i,j,k)=
(t2.a,t2.b,t2.c,t2.d,t2.e,t2.f,t2.g,t2.h,t2.i,t2.j,t2.k)
from table2 t2
where table1.id=table2.id
Or something like that, rather than like so:
update table set a=t2.a,b=t2.b etc
which can be pretty tiresome to write if you have 100+ columns.
Source: (StackOverflow)
I have two tables, both looking like
id name value
===================
1 Joe 22
2 Derk 30
I need to copy the value of value
from tableA
to tableB
based on check name in each table.
Any tips for this UPDATE
statement?
Source: (StackOverflow)
I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status.
So I'm thinking:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
But my brain hurts going any farther than that.
Source: (StackOverflow)
I have two tables, and want to update fields in T1 for all rows in a LEFT JOIN.
For an easy example, update all rows of the following result-set:
SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.id = T2.id WHERE T2.id IS NULL
The MySQL manual states that:
Multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.
But I cannot find the proper syntax for doing that in the documented multiple-tables UPDATE.
What is the proper syntax?
Source: (StackOverflow)
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.
I created a view linking the table to the account/card database to return the Table ID and the related account number, and now I need to update those records where the ID matches with the Account Number.
This is the Sales_Import
table, where the account number field needs to be updated:
LeadID AccountNumber
147 5807811235
150 5807811326
185 7006100100007267039
And this is the RetrieveAccountNumber
table, where I need to update from:
LeadID AccountNumber
147 7006100100007266957
150 7006100100007267039
I tried the below, but no luck so far:
UPDATE [Sales_Lead].[dbo].[Sales_Import]
SET [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber
FROM RetrieveAccountNumber
WHERE [Sales_Lead].[dbo].[Sales_Import]. LeadID =
RetrieveAccountNumber.LeadID)
It updates the card numbers to account numbers, but the account numbers gets replaced by NULL
Source: (StackOverflow)
I want to update a column in a table making a join on other table e.g.:
UPDATE table1 a
INNER JOIN table2 b ON a.commonfield = b.[common field]
SET a.CalculatedColumn= b.[Calculated Column]
WHERE
b.[common field]= a.commonfield
AND a.BatchNO = '110'
But it is complaining :
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'a'.
What is wrong here?
Source: (StackOverflow)
I want to update Top 100 records in sql server . I have a table T1 with fields F1 and F2. T1 has 200 records. I want to update F1 field of Top 100 records.
How can i can update in sql server.
Thanks
Based on comments there is a where clause that prevents re-processing the same records.
Source: (StackOverflow)