Tag Info

New answers tagged

1

Get rid of UNIX_TIMESTAMP() everywhere. DATETIME fields can be compared directly. Once you have done that, you will probably notice the missing @ that is causing the error. I think it can be simplified down to only: CREATE TRIGGER ... IF COALESCE(OLD.updateDate, OLD.insertDate) < CURRENT_DATE() - interval 3 month INSERT INTO ...


3

As answered by ypercube in a comment to the question There are one or more trigger(s) on your table. The multiple (1 row(s) affected) messages are generated not just by your single-row update statement, but also by other statements in the triggers fired from that original action.


0

In next iteration, you may need to check if there are pending numbers to send sms. If pending count is greater than zero then need to send sms to those pending numbers. track success_count after resending sms to pending numbers. Then, need to increment delivered count and decrement pending count with this success_count. So, delivered = delivered + ...


0

Write PHP/Perl/Java/VB/whatever code to SELECT from one db server and INSERT/UPDATE/whatever into the other. For "timed", use cron (if on *nix).


2

The composite index on (state, city) will not be used if you use the function trim in your query. You may want to update both field in both tables first: UPDATE orders SET oState=TRIM(oState), oCity=TRIM(oCity); UPDATE cities_extended SET state_code=TRIM(state_code), city=TRIM(city); Then run the query without trim On a side note, your index is better to ...


1

Part of the problem is that DOUBLE(8,2) rounds to 2 decimal places, thereby giving a different value than DOUBLE. Do not use (m,n) on FLOAT or DOUBLE. Suggest you make that change before doing the UPDATEs. Since you are talking about cities, DOUBLE is gross overkill and takes 16 bytes for the pair. DECIMAL(4,2) for latitude and DECIMAL(5,2) would add up ...


3

First, you have conditions duplicated in the ON and the WHERE clause. That is not needed, the condition are only needed once, preferably in the ON clause as they are used for the join of the two tables. But that is not what's causing the slow execution (and the lost connections). The reasons for being slow are three: you are joining a 63K with a 363K ...


2

Try this statement. UPDATE ProcurementPortal.orders SET orders.lat = geo.latitude, orders.lon = geo.longitude FROM ProcurementPortal.orders AS orders INNER JOIN ProcurementPortal.cities_extended AS geo ON orders.city = geo.city AND orders.state = geo.state_code


1

Your Original Query INSERT INTO example (a, b, c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c); If you consider (a,b,c) a unique key, there are two things you need to do First, add a unique index ALTER TABLE example ADD UNIQUE KEY abc_ndx (a,b,c); so the table structure would become CREATE TABLE IF NOT EXISTS ...


2

Well this is the insert bit that you are using: INSERT INTO example (a, b, c) VALUES (1,2,3) .... here you are not specifying the id (the primary key to be checked for duplication). Since, it is set to auto-increment, it automatically sets the id for the next row with just the values for columns a, b and c. The row in this case is updated when you ...


0

You simply need to use SQL Server String Concatenation. UPDATE YourTableName SET ColumnName = ColumnName+'_ERR' WHERE CoumnID<1 --- Apply filter if any required in your case


3

It's going to be a different concatenation operator depending on the RDMS you're using. For instance: Oracle uses || SQL Server uses + MySQL uses concat ...but everything else looks like: UPDATE table1 SET order = order ||'_ERR' WHERE [your condition] Since the question is tagged for SQL Server, the applicable syntax is: UPDATE table1 SET order = ...


1

OPTIMIZE TABLE is essentially never needed for InnoDB. New with 5.6: ALTER TABLE uses ALGORITHM=INPLACE for many actions (add/drop column/index, etc.). I would expect 5.6 to be faster. What is that list of commands trying to do? Something to do with Normalization? Seems like it could be done in a couple of queries, without needing the extra table. ...


3

Well, assuming that someone is holding a gun to your head, forcing you to not change the data type to something more sensible and logical: UPDATE CCCINC.dbo.Employees SET DateOfBirth = CONVERT(CHAR(8), DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 41975), '1900-01-01'), 112); Strong, strongly, strongly suggest you change the data type of that column to use the ...


1

You have PK clustered index as a uniqueidentifier? That index has massive fragmentation. You want a PK that is inserted in the order of the PK Why not just use an identity? Why are are you updating a value rather than just insert the correct value? Are you inserting just one row at a time? One row one round trip is not efficient. Fast inserts with ...


0

You can't have 'missing fields' in a query without getting a prompt. Your June updates can not reference the phone column that does not exist and your July can not reference address. You need 2 different queries. If your business logic can't determine which query to run then you can use the Fields Collection of the TableDef object.


1

[Note: I got to the end of writing this and noticed that sometimes you refer to the data field as image and sometimes as attach_data. You'll need to take that into account as you read this.] You might try breaking up the update statement into multiple batches with TOP: UPDATE TOP (5) messages SET attach_data = 0x WHERE ins_date < DATEADD(DAY, -120, ...


0

Consider indexing properly and running the cleanup once every minute. That way each batch is automatically tiny. Each day has 1440 such small batches then.


0

At 250/hour, all possible techniques are plenty fast. Anyway, I will throw my 2 cents in... Multi-row INSERT of 100 rows will run 10 times as fast as 100 individual INSERTs. Multi-row INSERT may lead to gaps in AUTO_INCREMENTs. In general, the command will preallocate all the ids it might need, then 'burn' the ones id did not use. (REPLACE them all dups ...



Top 50 recent answers are included