0
голосов
1ответов
174 просмотров

MySQL - Search and replace table1.field's contents with contents of table2.otherfield

I need a MySQL query that will do the following: Instead of the alpha team names in the 3rd column of the players table, I want it to reference teams.id table: players id player_name team ------------------------------------------ 1 Sue Smith Silly Chimps 2 ...

4
голосов
1ответов
3962 просмотров

Update a sorting index column to move items

If I have the following table & data to allow us to use the sort_index for sorting: CREATE TABLE `foo` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `bar_id` INT(11) DEFAULT NULL, `sort_index` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO `foo` (`bar_id`, `sort_index`) VALUES ...

1
голосов
5ответов
544 просмотров

Do UPDATE in SQL using a JOIN

I am using SQL Server 2008, and trying to run the following query in management studio: UPDATE Table1 as T1 INNER JOIN Table2 as T2 ON T1.ID=T2.ID SET T1.SomeValue = T2.SomeValue GO Doesn't work though. Is this sort of thing supported? Karl [EDIT] Let me just...

0
голосов
2ответов
415 просмотров

LINQ doesn't recognize changes in the database

I did not find something similar so I have to ask: I use LINQ to SQL and all was fine until i started to use Stored Procedures to update the database entries. (My Stored Proc is something like update all entries which groupid x) The Update runs fine and the values in the database change. But th...

16
голосов
8ответов
20468 просмотров

T-SQL conditional UPDATE (v2)

I have a table: Message (MessageID int, Subject nvarchar(100), Body nvarchar(max)) After a message is being updated on UI, I call a stored proc to update that table. In some cases user might update just subject, in other cases just body. I want this stored proc to only update what has changed,...

0
голосов
1ответов
498 просмотров

T-SQL conditional UPDATE

If I have an update statement like this: UPDATE Message SET Subject = Subject ...will SQL Server actually perform an update or is it smart enough to recognize that no update is really needed?

1
голосов
5ответов
1943 просмотров

PostgreSQL 8.2: Require specific column to be present in UPDATE statement

I would like to force user to specify origin of update to some table (sometbl), eg. to specify 'local' or 'remote' (for col2) - but checking of that requirement should occur at DB level when UPDATE statement is executed so: UPDATE sometbl SET col1 = 'abc'; should throw error (exception), but: ...

2
голосов
2ответов
8414 просмотров

Creating SQL batch updates from within Java

I want to update every row on a specific column in a mySql database. Currently I am using a java.sql.PreparedStatement for each row and iterating in a for loop. I was wondering if there were any other alternatives in terms of Java programming to make this less time and resource consuming (somethi...

1
голосов
1ответов
478 просмотров

Bidirectional replication update record

I would like to present you my problem related to SQL Server 2005 bidirectional replication. What do I need? My teamleader wants to solve one of our problems using bidirectional replication between two databases, each used by different application. One application creates records in table A, cha...

0
голосов
2ответов
860 просмотров

How can I conditionally update a text/ntext field in SQL?

In a stored procedure I'm trying to conditionally update a field (like the 2nd line in the SQL statement below) UPDATE [some_stuff] SET last_update = CASE WHEN val = @NewVal THEN last_update ELSE GETDATE() END, val = @NewVal ...but for a text/ntext field. What's the most efficient way to g...

2
голосов
2ответов
3313 просмотров

Multi-table Update(MySQL)

I have a question regarding multi-table update(MySQL). Consider table t1 and t2. The PKEY for t1 is 'tid' which is a foreign Key in t2. There is a field "qtyt2" in t2 which depends on a field called "qtyt1" in table t1. Consider the following SQL statement: UPDATE t2,t1 SET t2.qtyt2=IF(( t2....

0
голосов
3ответов
493 просмотров

SQL Update - Is there a more elegant and efficient way of doing this?

I have 3 tables Site, Price and PriceMonth. Site has many Prices (joined on siteId). PriceMonth holds a date which is a looked up by Price (joined on PriceMonth). The idea is that the price records are created when a site is created as place holders. When a user clicks a Price row in a view...

28
голосов
2ответов
47565 просмотров

update multiple rows using limit in mysql?

UPDATE messages set test_read =1 WHERE userid='xyz' ORDER BY date_added DESC LIMIT 5, 5 ; I am trying to use this query to update a set of 5 rows using limit but mysql is showing an error..The one below is working UPDATE messages set test_read =1 WH...

4
голосов
6ответов
686 просмотров

Performing an UPDATE or INSERT depending whether a row exists or not in MySQL

In MySQL, I'm trying to find an efficient way to perform an UPDATE if a row already exists in a table, or an INSERT if the row doesn't exist. I've found two possible ways so far: The obvious one: open a transaction, SELECT to find if the row exists, INSERT if it doesn't exist or UPDATE if it e...

0
голосов
4ответов
2103 просмотров

How to update DB structure when updating production system without doing a teardown / rebuild

If I'm working on a development server and have updates to the database structure for some of our releases, what is the best way to update the structure on the production server? Currently we create a new production database containing the structure only, do a SQL dump of the data on the 'old' p...

0
голосов
4ответов
192 просмотров

Adding update SQL queries

I have a script that updates itself every week. I've got a warning from my hosting that I've been overloading the server with the script. The problem, I've gathered is that I use too many UPDATE queries (one for each of my 8000+ users). It's bad coding, I know. So now I need to lump all the data...

3
голосов
5ответов
7496 просмотров

Is there a fast way to update many records in SQL?

I need to replace more than 20 000 names with new names i created given the CodeID. For example: I must update all rows that contain "dog" (which has a CodeID of 1) with "cat", and update all rows that contain "horse" (which has a CodeID of 2) with "bird", etc. 1st SQL statement: UPDATE anima...

6
голосов
5ответов
31043 просмотров

SQL - WHERE clause on each SET command in UPDATE?

I'm trying to create an SQL query in PHP to update a table. Is it possible to have a different WHERE clause for each affected row? eg something like: UPDATE table SET val=X WHERE someproperty = 1, SET val=Y WHERE someproperty = 2 etc? Any help appreciated. Thanks

0
голосов
6ответов
291 просмотров

Syntax error on SQL update statement

How can I correct the following so that I don't receive a syntax error in Microsoft SQL Server 2005? UPDATE Emp E SET UserName = Left(FirstName,1)+LastName WHERE EmpID=1 AND NOT EXISTS( SELECT * FROM Emp WHERE UserName=Left(E.FirstName,1)+E.LastName )

0
голосов
2ответов
701 просмотров

Prevent Typed DataSet updating PK column

I have a typed dataset, and where I call TableAdapter.Update(DataRow), the SQL executed includes an update of the primary key column of the database. Unfortunately, when this code runs on a replicated database, the PK column has the rowguid property set, and I get the following error: System...

5
голосов
4ответов
2199 просмотров

SQL Mass Reordering of rows

I have a table with a list of records, and a column called order. I have an AJAX script to drag and drop the table rows up or down which I want to use to perform a query, reordering the rows as they have been dragged. In the PHP, I perform a query to get the current order of the records. eg 1,...

18
голосов
7ответов
158152 просмотров

Round up value to nearest whole number in SQL UPDATE

I'm running SQL that needs rounding up the value to the nearest whole number. What I need is 45.01 rounds up to 46. Also 45.49 rounds to 46. And 45.99 rounds up to 46, too. I want everything up one whole digit. How do I achieve this in an UPDATE statement like the following? Update product SET...

9
голосов
6ответов
19458 просмотров

How to UPDATE TOP 400?

I would like to update the top 400 rows in a database table. The pseudo SQL is below, how can I do this? UPDATE top (400) db.dbo.tbl SET column1 = 2 WHERE column2 = 1 AND column1 is null

1
голосов
1ответов
5571 просмотров

DB2 Increment a value by a certain amount

I need to write a query that increments a value in a table by 3 when run. I would like to do something like this but this doesn't work. UPDATE table SET value = (SELECT value FROM table WHERE condition = true) + 3 WHERE condition = true As in the title this is a DB2 d...

1
голосов
1ответов
418 просмотров

JDBC: best way to update a table?

Let's say that I get a ResultSet which was queryed using joins from an sql database. Is it better to use an sql statement to update the tables or insert new tuples/rows? Or should i use the resultSet.update() function? I just want to know the drawbacks associated with each as well as the prefe...

1
голосов
6ответов
10185 просмотров

Update column with values from another column

I have a table like this: create table foo ( a number, b number ) I want to update all the columns from a with the value that is in another table create table bar ( x number, y number ) So, if this would be a procedural programing language I would: foreach foo_item in foo foreac...

0
голосов
2ответов
117 просмотров

SQL Query fails to update

I am trying to update a row on an SQL SERVER 2005. When I run the SQL, I receive a message indicating that the Execution was successful and 1 row was affected. However, when I do a select against this row I supposedly updated, the value remains unchanged. What's going on with this SQL server w...

1333
голосов
16ответов
1589780 просмотров

How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.assid. What query will do this? I'm thinking of a join but I'm not sure ...

1
голосов
1ответов
254 просмотров

SQL UPDATE Query

I have One main table( Say main) and another Temp Table( say copy). What I am trying to do is averages and Standard Deviation of each Stored proc( there are bunch of them with different version number as suffix and they all has to be treated same) from main table and update Temp table with averag...

40
голосов
6ответов
122586 просмотров

SQL Update Multiple Fields FROM via a SELECT Statement

This works, but i would like to remove the redundancy. Is there a way to merge the update with a single select statement so i don't have to use vars? DECLARE @OrgAddress1 varchar, @OrgAddress2 varchar, @OrgCity varchar, @OrgState varchar, @OrgZip varch...