New answers tagged

2

The problem in your query is this part: ON DUPLICATE KEY UPDATE -- paramvalue = t.extension ; The alias t is not visible in that part of the query. However, assuming that the two values, the one to be inserted in the params.extension column (12345) and the one checked against the params_extensions column (WHERE t.extension = '12345') are the ...


-1

I ended up creating the following trigger: DECLARE paramvalue_ VARCHAR(50); DECLARE msg VARCHAR(255); CREATE TEMPORARY TABLE IF NOT EXISTS params_temp(extension integer, type varchar(50), subtype varchar(50), paramvalue varchar(50), active integer, PRIMARY KEY (`extension`,`type`,`subtype`), UNIQUE KEY `extension_type` (`extension`,`type`)); DELETE FROM ...


5

You can do this much simpler using a CTE: ;WITH x AS ( SELECT Field1, Field2, c = COUNT(*) OVER(PARTITION BY Field1, Field2) FROM dbo.Table1 WHERE Field2 <> '' ) UPDATE x SET Field2 = '' WHERE c > 1; Or with a more cumbersome (and potentially less efficient, did not test) self-join: UPDATE t SET Field2 = '' FROM dbo.Table1 AS ...


2

Found the answer. Use a temp table and then inner join: SELECT [t].[Field1], [t].[Field2], COUNT([t].[Field2]) AS Counted INTO #TempTable FROM [dbo].[Table1] AS [t] WHERE [t].[Field2] <> '' GROUP BY [t].[Field1], [t].[Field2] HAVING COUNT([t].[Field2]) > 1 UPDATE [dbo].[Table1] SET [Field2] = '' FROM [dbo].[Table1] AS [t] ...


2

There are quite a few ways to achieve your desired results. Undeterministic methods (in the event that many rows in table 2 match one in table 1) UPDATE T1 SET address = T2.address, phone2 = T2.phone FROM #Table1 T1 JOIN #Table2 T2 ON T1.gender = T2.gender AND T1.birthdate = T2.birthdate Or a slightly more concise ...


0

Making the correct join is key. UPDATE table1 INNER JOIN table2 ON table1.birthdate = table2.birthdate AND table1.gender = table2.gender SET table1.address = table2.address, table1.phone = table2.phone ; As ypercubeᵀᴹ has mentioned, if you have duplicates in the second table then an arbitrary record will be picked for the update..


1

UPDATE text2bid_users SET valid_phone = CASE WHEN phone != '0000' THEN 0 else valid_phone END, phone = '0000' WHERE user_id = 1; http://sqlfiddle.com/#!9/24784/1


0

From the manual here, you can simply use the DATE() function. DATE(expr) Extracts the date part of the date or datetime expression expr. mysql> SELECT DATE('2003-12-31 01:02:03'); -> '2003-12-31' It is designed for precisely your use case. Although the quote is from the 5.7 manual, it works right back to at least 5.0. You don't have to ...


2

Join to a subquery that computes numero with the window function row_number(): UPDATE movimientos m SET numero = sub.rn FROM (SELECT id, row_number() OVER (ORDER BY orden, id) AS rn FROM movimientos) sub WHERE m.id = sub.id; Details for UPDATE syntax in the manual. If you have concurrent write access you need to lock the table to avoid race ...


4

If your table is test, try this: SELECT t1.* ,New_FileName = t1.FileName + CASE WHEN t2.seq > 1 THEN '_' + ltrim(str(seq)) ELSE '' END FROM test t1 JOIN ( SELECT ID ,Memeber_Number ,FileName ,seq = ROW_NUMBER() OVER ( PARTITION BY FileName ,Memeber_Number ORDER ...


0

It depends on your dialect of SQL, many times I have seen the following approaches: Make a temporary table, insert the rows, and use the PK of the generated table to update the existing values. Use windowing function, concatenate the values in a subquery and update the main table. For an example of windowing functions and subqueries in T-SQL(if you ...


14

Is this even true? No, it is an implementation detail. A database may implement a suitable update in place if it so chooses. What are the benefits? Splitting an update into a delete followed by an insert generally makes the implementation simpler. Potential side benefits include the ability to avoid transient key violations in a unique index, by ...


1

Ultimately - a dump of the table and reload solved the weird cardinality and row count behavior. We tried using analyze table, but that did not fix the problem. George's answer is very well done, but unfortunately it wouldn't solve my problem.


4

Unlike InnoDB, TokuDB historically did not automatically compute cardinality statistics. As a user you were required to manually run ANALYZE TABLE in order to calculate these values. All tables and indices created prior to 5.6.27-76.0 would also not maintain accurate row counts. After 5.6.27-76.0, new tables and indices, and tables that had RECOUNT ROWS ...



Top 50 recent answers are included