Updating a lot of Data in One Field using Where clause
I am trying to update one filled with a lot of data.
This is my table
I want to update
NameAlias with new names for
Major but this is my question if I have a lot of major names and ids how can I write a query that is good and short?
I know it should be something like this for one column.
Update Major Set NameAlias='Mathemathic' where IdMajor=1;
But what should I do if I have a lot of data for one field?
I want my result to be something like this:
You should create a temp table containing the
id, NameAlias accordingly like below.
select Id, NameAlias into #TempTable from ( values(1, 'Mathemathic'), (2, 'Software'), (3, 'Painthing') -- The rest of values )v(Id, NameAlias)
Then you can update the
Update m SET m.NameAlias = t.NameAlias From Major m INNER JOIN #TempTable t on m.IdMajor = t.Id
How to Update a Column Based on a Filter of Another , So to perform an update based on the value of another column in the same table, clause as much as you'd like in order to filter down the rows for updating to� If you can segment your data using, for example, sequential IDs, you can update rows incrementally in batches. So for example if table_one an id column you could add something like. and table_one.id between x and y to the where condition and run the query several times changing the values of x and y so that all rows are covered.
You can use case when expression -
Update Major Set NameAlias= case when namemajor='Math' then 'Mathemathic' when namemajor='Computer' then 'Software' when namemajor='Art' then 'Painthing' when namemajor='History' then 'FranceHistory' when namemajor='Music' then 'Piano' end
Use SQL UPDATE to Query and Modify Data, The SQL UPDATE statement is used to modify column values within a SQL Server table. Though an update statement can modify columns data from many sources, such Before we go much further, let's first wipe out the city column values. UPDATE Multiple Records. It is the WHERE clause that determines how many records will be updated. The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico":
There is no need to create a temporary table to do this. Just use a derived table in the
update m set m.NameAlias = v.newAlias from Major m join (values(1, 'Mathematic'), (2, 'Software'), (3, 'Painting') ) v(idMajor, newAlias) on m.IdMajor = v.IdMajor;
SQL UPDATE Statement - Updating Data in a Table, To update data in a table, you need to: First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. Third, specify which rows you want to update in the WHERE clause. To update values in multiple columns, you use a list of comma-separated assignments by supplying a value in each column’s assignment in the form of a literal value, an expression, or a subquery. Third, specify which rows to be updated using a condition in the WHERE clause. The WHERE clause is optional. If you omit it, the UPDATE statement will modify all rows in the table. Notice that the WHERE clause is so important that you should not forget.
MySQL UPDATE Statement, Let's practice the UPDATE statement. 1) Using MySQL UPDATE to modify values in a single column example. See the following employees table from the sample � The SQL UPDATE syntax. The general syntax is. UPDATE table-name. SET column-name1 = value1, column-name2 = value2, To limit the number of records to UPDATE append a WHERE clause: UPDATE table-name. SET column-name1 = value1, column-name2 = value2,
How to specify the WHERE clause for UPDATE / DELETE ?, or DELETE statement. UPDATE table SET column = newvalue Controlling UPDATE's / DELETE's. Choose one of the following in the Where Clause for Update / Delete. The results are illustrated by an example following the table. The trick is to join the two tables using a common field and to properly specify the name of the field from the source table in the Update To cell. You can update more than one field at a time in an update query. You can also include additional fields in the query grid to further limit the rows to be updated.
SQL provides the UPDATE statement for changing data in a table. By using a single UPDATE statement, you can change one, some, or all rows in a table. The UPDATE statement uses the following syntax: UPDATE table_name SET column_1 = expression_1, column_2 = expression_2, , column_n = expression_n [WHERE predicates] ; The WHERE clause is
- what is the last then? if id remove it I get the error
- From my point of view, using
CASE WHENis not good for coding practice.
- @Phong is it ok to use it when I have a lot of data too?
- @Nazanin Did you check my answer ?
- @Fahmi . . . I'm upvoting because creating a temporary table to do this is utterly unnecessary.
- what is t.Idmajor?? we do not have this
- @Nazanin . . . That should be
v. I fixed it.