SQL Server - Update Column from Data in the Same Table

SQL Server - Update column from data in the same table

It's not clear which 2012 value you want to use to update which 2013 value, i've assumed that the ID should be the same.

Full example using table variables that you can test yourself in management studio.

DECLARE @Tbl TABLE (
    SetId INT,
    Id INT, 
    Premium VARCHAR(1)
)

INSERT INTO @Tbl VALUES (2012, 5, 'Y')
INSERT INTO @Tbl VALUES (2012, 6, 'Y')
INSERT INTO @Tbl VALUES (2013, 5, 'N')
INSERT INTO @Tbl VALUES (2013, 6, 'N')

--Before Update
SELECT * FROM @Tbl 

--Something like this is what you need
UPDATE t 
SET t.Premium = t2.Premium 
FROM @Tbl t 
INNER JOIN @Tbl t2 ON t.Id = t2.Id 
WHERE t2.SetId = 2012 AND t.SetId = 2013

--After Update    
SELECT * FROM @Tbl 

Update in the same column from the same table

We could try using COALESCE to conditionally replace a NULL year of birth with a non NULL value:

SELECT
    ID,
    EMPLOYEE_ID,
    COALESCE(YEAR_OF_BIRTH, MAX(YEAR_OF_BIRTH) OVER (PARTITION BY EMPLOYEE_ID)) AS YEAR_OF_BIRTH,
    PROJECT_ID
FROM yourTable;

SQL UPDATE on with data from same table

Asuming [SN] is being augmented with -RG

Example

with cte as (
Select * 
      ,NV = max(FID) over (partition by replace(SN,'-RG','') )
 From YourTable
)
Update cte set FID = NV 
 --Where FID is null  -- Optional

The Updated Table

ID  SN          FID
1   12345       1
2   1122        2
3   12345-RG    1
4   1122-RG     2

SQL Server update a table based on values in the same table

Can you include some result rows where language ends up as NULL? And also how those rows looked like before?

To end up as NULL, the JOIN, in the subquery, would have to produce 0 rows or that there already are rows in 'myTable' where language IS NULL.

P.S. Easier when you indent and clean your code:

UPDATE 
    myTable
SET 
    myTable.language = t2.language 
FROM 
    myTable t2
WHERE 
    myTable.language    = '--' AND
    myTable.book        = t2.book AND
    myTable.firstRun    = t2.lastRun + 1

SQL Server : update column with value from same table based on choice of values

You can use a correlated update.

UPDATE t2
SET qty = (select SUM(qty) from Inventory where item_id=t2.item_id and ENTRYTYPE = 20)
FROM Inventory t2
where ENTRYTYPE=10 AND qty=0

To restrict the item_id's that get updated, use a where condition.

UPDATE table column with latest related id from the same table

Key to better performance (especially for your original query) is an index with inverted index columns. While being at it, make it UNIQUE:

CREATE UNIQUE INDEX candles_idx1 ON public.candles (minute, day);

Equality column first. See:

• Multicolumn index and performance
• Is a composite index also good for queries on the first field?
• Working of indexes in PostgreSQL

If the index cannot be UNIQUE, you have to tell us more about possible duplicates and how you intend to break ties.

If it can, consider using it as PK to replace the id column (completely). You may want an additional index on (day, minute) ...

While updating all rows, it should be (much) faster to join to a single subquery with the window function lag() in a FROM clause to compute all target values (instead of running a correlated subquery for every row):

UPDATE candles c
SET    id_d1 = c2.prev_id
FROM  (
   SELECT id, lag(id) OVER (PARTITION BY minute ORDER BY day) AS prev_id
   FROM   candles
   ) c2
WHERE  c.id = c2.id

If some rows can already have a correct id_d1, add this line to avoid costly empty updates:

AND    id_d1 IS DISTINCT FROM c2.prev_id

See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

While updating all rows, the index will probably not even be used with the new query.

With the index in place, consider dropping id_d1 from the table completely. Storing functionally dependent values tends to be a bad idea. Computing it on the fly with lag() should be cheap. Then the value is always up to date automatically. Otherwise you have to think about how to keep the column up to date - which may be tricky.

Update row with select on same table

You don't specify the database. The following is standard SQL:

UPDATE t
    SET TEXT = (SELECT text
                FROM t t2
                WHERE t.id = t2.id AND LANG ='EN' AND
                      TEXT IS NOT NULL
               ) 
    WHERE TEXT IS NULL;

In the event of duplicates, the following should work:

UPDATE t
    SET TEXT = (SELECT max(text)
                FROM t t2
                WHERE t.id = t2.id AND LANG ='EN' AND
                      TEXT IS NOT NULL
               ) 
    WHERE TEXT IS NULL;

EDIT:

Of course, not all databases support all ANSI standard functionality. In MySQL, you would use a join instead:

UPDATE t JOIN
       (SELECT id, max(text) as text_en
        FROM t t2
        WHERE LANG ='EN' AND TEXT IS NOT NULL
       ) ten 
       ON t.id = ten.id
    SET t.TEXT = ten.text_en
    WHERE t.TEXT IS NULL;

SQL - UPDATE (Using existing rows in same table)

I think your conditions simplify to getting the earliest id2 from a matching id1 or id3:

update t
    set id2 = tt.new_id
    from (select t.*,
                 (select t2.id2
                  from t t2
                  where t2.id1 = t.id1 or t2.id3 = t.id3
                 ) as new_id
          from t
         ) tt
    where t.id1 = tt.id1;

Update values from one column in same table to another in SQL Server

This works for me

select * from stuff

update stuff
set TYPE1 = TYPE2
where TYPE1 is null;

update stuff
set TYPE1 = TYPE2
where TYPE1 ='Blank';

select * from stuff

MSSQL: Update value from another row and another column from the same table based on conditions

A simple update with an inner join should do the trick:

UPDATE f1 
SET qty_ref = f2.qty 
FROM fact_sales f1 
INNER JOIN dim_item_ref d ON(f1.item = d.item)
INNER JOIN fact_sales f2 ON(d.item_ref = f2.item)
WHERE f1.site = f2.site
AND f1.date = f2.date

---

Related Topics