Update One Table with Data from Another

update one table with data from another

For MySql:

UPDATE table1 JOIN table2 
    ON table1.id = table2.id
SET table1.name = table2.name,
    table1.`desc` = table2.`desc`

For Sql Server:

UPDATE   table1
SET table1.name = table2.name,
    table1.[desc] = table2.[desc]
FROM table1 JOIN table2 
   ON table1.id = table2.id

How do I UPDATE from a SELECT in SQL Server?

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

update one table with data from another using derived key value

There is no such thing as "rownum" in a table. SQL tables represent unordered sets, so there is no ordering without an ordering column.

Oracle does provide rowid as a built-in column identifier. This is not the same as rownum and is not guaranteed to be in order.

You can use rownum, but the value is not guarantee to have any particular meaning and might change between runs:

update table1
    set (name, desc) = (select name, desc
                        from (select t2.*, rownum as seqnum
                              from table2 
                             ) t2
                        where seqnum = table1.id
                       )
    where id <= (select count(*) from table2);

How to update column in a table from another table based on condition?

Why to use sub-query when you can do that directly?

UPDATE st
  SET st.school_code = sc.school_id 
FROM master.student AS st
  JOIN Master.school AS sc
ON st.school_code = sc.school_code
WHERE sc.year=x
  AND st.year=x;

For more info See UPDATE (Transact-SQL)

How can I UPDATE a table based on another table, using values from groups of rows?

Getting a value from a column different to the column used in a MIN/MAX expression in a GROUP BY query still remains a surprisingly difficult thing to do in SQL, and while modern versions of the SQL language (and SQL Server) make it easier, they're completely non-obvious and counter-intuitive to most people as it necessarily involves more advanced topics like CTEs, derived-tables (aka inner-queries), self-joins and windowing-functions despite the conceptually simple nature of the query.

Anyway, as-ever in modern SQL, there's usually 3 or 4 different ways to accomplish the same task, with a few gotchas.

Preface:

• As Site, Date, Year, and Month are all keywords in T-SQL, I've escaped them with double-quotes, which is the ISO/ANSI SQL Standards compliant way to escape reserved words.

  • SQL Server supports this by default. If (for some ungodly reason) you have SET QUOTED IDENTIFIER OFF then change the double-quotes to square-brackets: []

• I assume that the Site column in both tables is just a plain' ol' data column, as such:

  • It is not a PRIMARY KEY member column.
  • It should not be used as a GROUP BY.
  • It should not be used in a JOIN predicate.

• All of the approaches below assume this database state:

CREATE TABLE "Employee" (
    "Site"     int      NOT NULL,
    WorkTypeId char(2)  NOT NULL,
    Emp_NO     int      NOT NULL,
    "Date"     date     NOT NULL  
);

CREATE TABLE "PTO" (
    "Site"     int      NOT NULL,
    WorkTypeId char(2)      NULL,
    Emp_NO     int      NOT NULL,
    "Date"     date     NOT NULL  
);

GO

INSERT INTO "Employee" ( "Site", WorkTypeId, Emp_NO, "Date" )
VALUES
( 5015, 'MB', 1005, '2022-02-01' ),
( 5015, 'MI', 1005, '2022-02-04' ),
( 5015, 'PO', 1005, '2022-02-04' ),
( 5015, 'ME', 2003, '2022-01-01' ),
( 5015, 'TT', 2003, '2022-01-10' );

INSERT INTO "PTO" ( "Site", WorkTypeId, Emp_NO, "Date" )
VALUES
( 5015, NULL, 1005, '2022-02-03' ),
( 5015, NULL, 1005, '2022-02-14' ),
( 5014, NULL, 2003, '2022-01-09' );

• Both approaches define CTEs e and p that extend Employee and PTO respectively to add computed "Year" and "Month" columns, which avoids having to repeatedly use YEAR( "Date" ) AS "Year" in GROUP BY and JOIN expressions.
  
  • I suggest you add those as computed-columns in your base tables, if you're able, as they'll be useful generally anyway. Don't forget to index them appropriately too.

Approach 1: Composed CTEs with elementary aggregates, then UPDATE:

WITH
-- Step 1: Extend both the `Employee` and `PTO` tables with YEAR and MONTH columns (this simplifies things later on):
e AS (
    SELECT
        Emp_No,
        "Site",
        WorkTypeId,
        "Date",

        YEAR( "Date" ) AS "Year",
        MONTH( "Date" ) AS "Month"
    FROM
        Employee
),
p AS (
    SELECT
        Emp_No,
        "Site",
        WorkTypeId,
        "Date",

        YEAR( "Date" ) AS "Year",
        MONTH( "Date" ) AS "Month"
    FROM
        PTO
),
-- Step 2: Get the MIN( "Date" ) value for each group:
minDatesForEachEmployeeMonthYearGroup AS (
    SELECT
        e.Emp_No,
        e."Year",
        e."Month",

        MIN( "Date" ) AS "FirstDate"
    FROM
        e
    GROUP BY
        e.Emp_No,
        e."Year",
        e."Month"
),
-- Step 3: INNER JOIN back on `e` to get the first WorkTypeId in each group:
firstWorkTypeIdForEachEmployeeMonthYearGroup AS (
    /* WARNING: This query will fail if multiple rows (for the same Emp_NO, Year and Month) have the same "Date" value. This can be papered-over with GROUP BY and MIN, but I don't think that's a good idea at all). */
    SELECT
        e.Emp_No,
        e."Year",
        e."Month",

        e.WorkTypeId AS FirstWorkTypeId
    FROM
        e
        INNER JOIN minDatesForEachEmployeeMonthYearGroup AS q ON
            e.Emp_NO = q.Emp_NO
            AND
            e."Date" = q.FirstDate
)
-- Step 4: Do the UPDATE.
-- *Yes*, you can UPDATE a CTE (provided the CTE is "simple" and has a 1:1 mapping back to source rows on-disk).
UPDATE
    p
SET
    p.WorkTypeId = f.FirstWorkTypeId
FROM
    p
    INNER JOIN firstWorkTypeIdForEachEmployeeMonthYearGroup AS f ON
        p.Emp_No = f.Emp_No
        AND
        p."Year" = f."Year"
        AND
        p."Month" = f."Month"
WHERE
    p.WorkTypeId IS NULL;

Here's a screenshot of SSMS showing the contents of the PTO table from before, and after, the above query runs:

Approach 2: Skip the self-JOIN with FIRST_VALUE:

This approach gives a shorter, slightly simpler query, but requires SQL Server 2012 or later (and that your database is running in compatibility-level 110 or higher).

Surprisingly, you cannot use FIRST_VALUE in a GROUP BY query, despite its obvious similarities with MIN, but an equivalent query can be built with SELECT DISTINCT:

WITH
-- Step 1: Extend the `Employee` table with YEAR and MONTH columns:
e AS (
    SELECT
        Emp_No,
        "Site",
        WorkTypeId,
        "Date",

        YEAR( "Date" ) AS "Year",
        MONTH( "Date" ) AS "Month"
    FROM
        Employee
),
firstWorkTypeIdForEachEmployeeMonthYearGroup AS (

    SELECT
        DISTINCT
        e.Emp_No,
        e."Year",
        e."Month",
        FIRST_VALUE( WorkTypeId ) OVER (
            PARTITION BY
                Emp_No,
                e."Year",
                e."Month"
            ORDER BY
                "Date" ASC
        ) AS FirstWorkTypeId
    FROM
        e
)
-- Step 3: UPDATE PTO:
UPDATE
    p
SET
    p.WorkTypeId = f.FirstWorkTypeId
FROM
    PTO AS p
    INNER JOIN firstWorkTypeIdForEachEmployeeMonthYearGroup AS f ON
        p.Emp_No = f.Emp_No
        AND
        YEAR( p."Date" ) = f."Year"
        AND
        MONTH( p."Date" ) = f."Month"
WHERE
    p.WorkTypeId IS NULL;

Doing a SELECT * FROM PTO after this runs gives me the exact same output as Approach 2.

Approach 2b, but made shorter:

Just so @SOS doesn't feel too smug about their SQL being considerably more shorter than mine , the Approach 2 SQL above can be compacted down to this:

WITH empYrMoGroups AS (
    SELECT
        DISTINCT
        e.Emp_No,
        YEAR( e."Date" ) AS "Year",
        MONTH( e."Date" ) AS "Month",
        FIRST_VALUE( e.WorkTypeId ) OVER (
            PARTITION BY
                e.Emp_No,
                YEAR( e."Date" ),
                MONTH( e."Date" )
            ORDER BY
                e."Date" ASC
        ) AS FirstWorkTypeId
    FROM
        Employee AS e
)
UPDATE
    p
SET
    p.WorkTypeId = f.FirstWorkTypeId
FROM
    PTO AS p
    INNER JOIN empYrMoGroups AS f ON
        p.Emp_No = f.Emp_No
        AND
        YEAR( p."Date" ) = f."Year"
        AND
        MONTH( p."Date" ) = f."Month"
WHERE
    p.WorkTypeId IS NULL;

• The execution-plans for both Approach 2 and Approach 2b are almost identical, excepting that Approach 2b has an extra Computed Scalar step for some reason.
• The execution plans for Approach 1 and Approach 2 are very different, however, with Approach 1 having more branches than Approach 2 despite their similar semantics.
• But my execution-plans won't match yours because it's very context-dependent, especially w.r.t. what indexes and PKs you have, and if there's any other columns involved, etc.

Approach 1's plan looks like this:

Approach 2b's plan looks like this:

@SOS's plan, for comparison, is a lot simpler... and I honestly don't know why, but it does show how good SQL Server's query optimizer is thesedays:

Update and insert to one table from another

Merge table2 as target
using table1  as source
on
target.id=source.id
When matched 
Then
update 
set target.id=source.id,
    target.name=source.name
When not matched by Target Then
INSERT (id, name) VALUES (id, name);

There are some issues with Merge statement,so it should be used with caution..

Further i recommend ,using merge as two seperate DML statements like below..

insert into table2
select * from table1 t1 where not exists (select 1 from table2 t2 where t2.id=t1.id)

update t2
set 
t2.id=t1.id,
t2.name=t1.name
from 
table1 t1
join
table2 t2
on t1.id=t2.id

Reasons being stated by Paul White here in his detailed answer..

mysql update column with value from another table

In addition to this answer if you need to change tableB.value according to tableA.value dynamically you can do for example:

UPDATE tableB
INNER JOIN tableA ON tableB.name = tableA.name
SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value)
WHERE tableA.name = 'Joe'

---

Related Topics