Tag Info

New answers tagged

3

Would a different kind of column be faster? For example an integer No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway. Is there some way to not lock the column? It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating. ...


0

SHOW ENGINE INNODB STATUS\G and see http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded increasing the lock timeout period could solve the issue. Apparently default is 50seconds. Which is maybe enough for all of the rows with userid=99, but not enough for rows with 99,22,88 or 7 userid. Just speculation.


0

And this is why you shouldn't use such a simple pattern :) Please see Ready-to-Use Database models example (s) for a list of references. An address should be its own entity. People -< PeopleAddresses >- Addresses The Sales Order points to the Address (as the Billing Address), and the Sales Order Line Items point to the Address (as the ShipTo ...


0

You create an extra table with customer ID and all other fields that will possibly change over the time. In the orders table, you link to the id of this new table, not to the costumers tables. customers(customer_id, first_name, last_name...) customer_info(id, customer_id, address, other field) orders(id, customer_info_id, order_date, ...etc)


0

SUGGESTION #1 If catgeory_id, family, text are the only three columns in the table, use REPLACE replace into foo (catgeory_id, family, text) values ('1', 'test', 'my text'), (50k more rows); REPLACE is nothing more that a mechanical DELETE and INSERT. SUGGESTION #2 If you are deleting 50k rows of category_id 1, then do a DELETE of all of them first ...


0

OK, so I'm not going to comment on the table design as I assume that this is just part of the tables. To do the update that you are looking for there's a couple of options. You can get the Permission ID and put it into a variable, then use that against the permission table. DECLARE @PermissionId INT SELECT @PermissionId = PermissionId from Employee where ...


0

I finally worked around the problem using SQL Server 2012 sequences. Unfortunately, this won't work with versions prior to 2012. Speed was o.k, one or two minutes for about 20 million IDs to insert. declare @old_max_id int -- get somehow declare @sql nvarchar(2000) set @sql=N'CREATE SEQUENCE INSERT_IDS START WITH ' + str(@old_max_id + 1) + N' INCREMENT BY ...


0

Actually, it is pretty easy to configure a delayed member of a replica set via the shell: cfg = rs.conf() cfg.members[0].priority = 0 cfg.members[0].hidden = true cfg.members[0].slaveDelay = 3600 rs.reconfig(cfg) In this example taken from the docs, the first member of replica set will be reconfigured to have a delay of one hour. Note that this delayed ...


0

You can use something like this UPDATE [table1_name] AS t1 INNER JOIN [table2_name] AS t2 ON t1.[column1_name] t2.[column1_name] SET t1.[column2_name] = t2.[column2_name]; for detailed tutorial http://voidtricks.com/mysql-inner-join-update/


2

Use a subquery in the FROM clause of the UPDATE: UPDATE server_info s SET status = 'active' FROM ( SELECT server_ip -- your pk column or any (se of) unique colum(ss) FROM server_info WHERE status = 'standby' LIMIT 1 -- arbitrary pick (cheapest) ) sub WHERE s.server_ip = sub.server_ip RETURNING server_ip; With heavy concurrent ...


0

I suggest a "data-modifying CTE": WITH cte AS ( SELECT *, row_number() OVER () AS rn FROM ( SELECT id FROM tbl ORDER BY random() LIMIT 600 -- 2 x 300 ) sub ) INSERT INTO table2(source, target) SELECT c1.id, c2.id FROM cte c1 JOIN cte c2 ON c2.rn = c1.rn + 300; In the CTE: select 600 random rows (to create ...


6

You can do this. WITH T AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID, ID FROM StagingTable WHERE ID IS NULL) UPDATE T SET ID = New_ID So the windowed function is used in the SELECT list but you can still use the result of it to ...



Top 50 recent answers are included