logo
down
shadow

SQL update table with distinct


SQL update table with distinct

By : user78333
Date : October 18 2020, 01:08 AM
help you fix your problem is it possible to do an update to table with Distinct statement? I have a query here that will map the entries from another table but table1 have duplicate entries. removing the distinct statement made the output wrong. , Is this what you are looking for
code :
SELECT * FROM TABLE2 INNER JOIN
(
  SELECT DISTINCT  [document_name] FROM TABLE1
)T ON TABLE2.Document_Name = T.Document_Name


Share : facebook icon twitter icon
Update column in sql table based on distinct values of other columns in same table

Update column in sql table based on distinct values of other columns in same table


By : juan bernardo Gomez
Date : March 29 2020, 07:55 AM
Any of those help I used to create ids using ROW_NUMBER(). I don't know if this is the easiest way to fill in the ids in your case, but it should work.
It may work with a single update, without temp table, but sometimes I find it easier to split operations in smaller steps.
code :
select
    ROW_NUMBER() OVER (order by strategy_name, symbol_name, test_interval, P1, P2, P3 asc) as id,
    strategy_name, symbol_name, test_interval, P1, P2, P3
INTO #ids
FROM [table]
GROUP BY strategy_name, symbol_name, test_interval, P1, P2, P3
UPDATE [table]
SET test_id = (select id from #ids where 
  [table].strategy_name = #ids.strategy_name, 
  and [table].symbol_name = #ids.symbol_name, 
  and [table].test_interval = #ids.test_interval, 
  and [table].P1 = #ids.P1, 
  and [table].P2 = #ids.P2, 
  and [table].P3 = #ids.P3)
Sql Update a table values from another table using distinct and where clause

Sql Update a table values from another table using distinct and where clause


By : Khiem Huynh Quang
Date : March 29 2020, 07:55 AM
I wish this helpful for you Here is the screnario, i have2 tables table1 and table2 and i need to update the table1 using table2 as there are quite a few duplicate in table2 but need to pick one using the where clause in table2
code :
update   t1
set      Source = 'SS'
,        PP = t2.MaxPP
from     Table1 t1
join     (
         select  Email_address
         ,       max(PP) as MaxPP -- Pick max PP from the available group
         from    Table2
         where   Source = 'SS'
         group by
                 Email_address
         ) t2
on       t2.Email_address = t1.Email_address
Update Table with DISTINCT SELECTed values from same TABLE

Update Table with DISTINCT SELECTed values from same TABLE


By : Maximiliano Castro D
Date : March 29 2020, 07:55 AM
should help you out Your SQL Fiddle makes the question much clearer. You want the minimum attribute id on all rows with the same value in the last column. You can do this with an update/join like this:
code :
UPDATE table1 JOIN
       (SELECT `name_en-GB`, min(Attr_Id) as minai
        from table1
        GROUP BY `name_en-GB`
       ) tt
       on table1.`name_en-GB` = tt.`name_en-GB`
   SET attr_id = tt.minai
 WHERE table1.`name_en-GB` IN ('Bride Name', 'Child Grade') AND
       table1.attr_id <> tt.minai;
How do you update a sql table based on distinct matching counts in another table?

How do you update a sql table based on distinct matching counts in another table?


By : William
Date : March 29 2020, 07:55 AM
hop of those help? You can't group by in your update statement. You just need to form the query separately that will give you the rows you need to update and join on that. You are updating based on ID, and the value you are setting depends on the number of open events for that ID, so form a query finding the number of open events by id:
code :
-- query open event counts by contact id
SELECT ID, COUNT(*) AS OpenEventCount
FROM Events
WHERE EventType = 'Open'
GROUP BY ID
UPDATE c
SET c.Tier = CASE
        WHEN COALESCE(ec.OpenEventCount, 0) > 2 then 'Max'
        WHEN COALESCE(ec.OpenEventCount, 0) = 2 then 'High'
        ELSE 'Low' 
    END
FROM Contacts c
    LEFT OUTER JOIN ( -- left join to update contacts with no open events
        SELECT ID, COUNT(*) AS OpenEventCount
        FROM Events
        WHERE EventType = 'Open'
        GROUP BY ID
    ) ec ON ec.ID = c.ID
Mysql- update row from one table based on distinct value from another table

Mysql- update row from one table based on distinct value from another table


By : Gaya3
Date : March 29 2020, 07:55 AM
this one helps. In a Derived Table, you can get the minimum value of MemberID for every UserID from the UserMembers table. Join this to the Users table using UserID, and set this minimum value in the Users table.
code :
UPDATE Users AS u
JOIN 
(SELECT UserID, MIN(MemberID) AS minMemberID
 FROM UserMembers 
 GROUP BY UserID
) AS dt 
  ON dt.UserID = u.UserID 
SET u.MemberID = dt.minMemberID
Related Posts Related Posts :
  • Get last 2 years value and oldest year SQL
  • Calculate contribution
  • SQL Server: force cleanup of deallocated internal objects in tempdb to release disk space reserved for them within an op
  • sql oracle in() clause
  • How to trim out letter in the column
  • Update field in one table based on select in another one
  • SQL Server : Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
  • How to calculate the RANK from another column than the Window order?
  • Add a constraint to all columns in all tables
  • Simplify SQL code
  • Oracle sql - sorting record with selected records on top
  • SQL Server 2016: Numbers Wildcard [^0-9] - filter out 0000
  • What is the most elegant way to store timestamp with nanosec in postgresql?
  • CASE WHEN SQL Syntax Error
  • Performance Issue - Select firstdate per month on a very big table
  • How to Group by with sum of multi row in another table
  • using exists clause in a sum case (oracle)
  • SQL: How to count number of each vowel separately in a string
  • How to sort data in stored procedure with column positions of select statement with CASE
  • regexp_substr: find the text before a given patern
  • How to create a view that can identify each person's club out of many club?
  • Create New Field QGIS Depending on Another Field Using Case
  • How can you find the beginning of a week for a given date in Netezza?
  • Automatically assigning a primary key to an entry in MS-Access via Netbeans
  • Convert OUTER APPLY to LEFT JOIN
  • I need DateTime in SELECT, but if in GROUP BY provides unusable report
  • Concatenate with string but exclude when null
  • Copy null data to not null in a column
  • ORA-01861: literal does not match format string 01861
  • Postgres query for calendar
  • Error in Join query with pagination for Oracle 10g
  • Replace %20 string in URL with Underscore using SQL Server Database Table
  • SQL how to replace multiple characters in a single field of the same table with single query
  • Why does this Postgres Select Query work in the local Docker Container but not on Amazon RDS Postgres?
  • Combine two Oracle SQL queries with one LEFT OUTER JOIN and two INNER JOINS from four tables
  • How to generate unique id based on start date and end date for the next 40 years in SQL table
  • Single SELECT Statement to Retrieve All Possible Combinations of 1 Columns Values Oracle
  • Stored procedure fails, but works correctly when it runs manually
  • T-SQL Null comparisons in stored procedure
  • SQL query returning error only when CASE WHEN is used
  • Last value based on criteria
  • SELECT assigns a value to local variable and refers it again. Are the results guaranteed by SQL Server?
  • Do 'set operations' have an prescribed order of execution, or do they execute in order of evaluation?
  • SQL Select with distinct last 10 characters
  • Delete duplicate records based on timestamps
  • PostgreSQL Find all matches for given player
  • oracle regular expression issue
  • MS Access VBA query multiple tables
  • Summing a Case Statement with a check for Value in Table Variable
  • selecting data for the first 12 months
  • How to convert Big Int into Datetime style 109
  • SELECT the record (time) of a specific change for one ID
  • Should I put a row number filter in join condition or in a prior CTE?
  • Nest a select statement inside array_to_json(array_agg(row_to_json())) in PostgreSQL
  • Unable to pull Custom Dimension for a specific ID using BigQuery Standard SQL
  • Count of id per day using window function
  • WHERE clause with the same field name
  • How to choose 1 record from 2 tables based on record existence?
  • How to do join only with distinct items with latest date
  • Convert MAC address to integer in Vertica
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com