Tag Info

New answers tagged

0

[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 ...


0

Take a look here. CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ); and also at this: CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );


-3

UPDATE TABLE_NAME SET ToDateTime = ContainedData WHERE AID = 123 AND PID = 2


2

It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages... something like this, which uses the language setting to control isdate and convert. set language english; update test_dates set ToDateTime = convert(datetime,ContainedData) where AID='123' and PID='2' and ...


2

How about this: UPDATE [dbo].[MyTable] SET [ToDateTime] = [ContainedData] WHERE aid = 123 and pid = 2 and isdate([ContainedData] ) = 1


1

I've made it in a TRIGGER calling a Stored Procedure inside resultsTBL table. Table lotteryTBL and resultsTBL: mysql> SELECT * FROM test.lotteryTBL; +----+------+------+------+------+------+------+-------------+ | ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew | +----+------+------+------+------+------+------+-------------+ | 1 | 1 | ...


0

It was an undocumented trigger that was causing the problem.


2

Looks like my short answer was not to everyone's taste, so let me show you the long answer First of all, let's create our lab. @WestFarmer mentioned a couple of tables SQL>create table A ( 2 some_column number, 3 another_column number 4 ); Table created. SQL>create table B ( 2 id number 3 ); Table created. Also a mentoned ...


1

I believe this can be solved relatively quickly, by adding a FROM clause to your UPDATE, and adding an additional equality condition, namely, adding user_info.user_login = user_contacts.user_login. UPDATE user_info SET password = '$2a$11$fwea...IEI' FROM user_contacts WHERE user_info.user_login = user_contacts.user_login AND user_contacts.email = ...


3

Your UPDATE statement is wrong. I've edited your TRIGGER and the modifications I did and errors I saw: Errors: You tried to drop a different TRIGGER that the one you're going to create. DROP TRIGGER IF EXISTS veevan.account_ai$$. Why do you need the variable @TargetLastMod?. You didn't use it in the whole trigger. Modifications: I edited the DROP ...


1

I've modified your TRIGGER and I used ON DUPLICATE KEY UPDATE to UPDATE all the fields when your NEW.lastmodifieddate be greater than targets.lastSyncAt. Some errors: You've used a function on a field label in pdone.LCAPITAL(first), that can't be possible. You've used the same function now trying to CONCAT 2 field labels, instead table fields name ...


3

If you do not have access to create Directory, then you can use SQL Loader to get data into the database (provided you have Oracle client installed on your system). An outline of the steps is provided below: a.) Create a table with the same structure as your excel file b.) Convert your Excel File to .csv format file c.) Create a Loader Control File. Refer ...


1

CREATE TABLE TABLE_ITEMS ( serial_num int null, item_group int null ) GO INSERT INTO TABLE_ITEMS VALUES (0,1), (1,1), (2,1),(3,1) ,(4,1) ,(0,2) ,(1,2), (2,2), (3,2), (4,2), (5,2), (0,3) ,(1,3) ,(2,3) GO SELECT Item_group, ROW_NUMBER() OVER (PARTITION BY item_group order by item_group) AS AddNum INTO #NewSerials from TABLE_ITEMS WHERE ...


1

Your syntax is slightly off. In SQL Server, here is how I would perform this update: UPDATE s SET s.city = c.city FROM dbo.Suppliers AS s INNER JOIN dbo.Customers AS c ON s.supplier_name = c.customer_name; From my most popular answer on Stack Overflow. Remember to always use the schema prefix and also it is often easier to just call those columns name ...


2

I have heard of concurrency problems like that in MySQL before. Not so in Postgres. Built-in row-level locks in the default READ COMMITTED transaction isolation level are enough. I suggest a single statement with a data-modifying CTE (something that MySQL also doesn't have) because it's convenient to pass values from one table to the other directly (if you ...



Top 50 recent answers are included