EzDevInfo.com

Update a column value, replacing part of a string

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?

How to insert a value that contains an apostrophe (single quote)?

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.

Number of rows affected by an UPDATE in PL/SQL

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.

T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition

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.

How to update column with null value

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?

MySQL, update multiple tables with one query

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?

UPDATE from SELECT using SQL Server

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

How to update only one field using Entity Framework?

Here's the table

Users

UserId
UserName
Password
EmailAddress

and the code..

public void ChangePassword(int userId, string password){
//code to update the password..
}

Update multiple columns in SQL

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.

mysql update column with value from another table

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?

How do I (or can I) SELECT DISTINCT on multiple columns?

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.

UPDATE multiple tables in MySQL using LEFT JOIN

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?

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

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

Update a table using JOIN in SQL Server?

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?

how can I Update top 100 records in sql server

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.