How can I do an UPDATE statement with JOIN in SQL Server

Question

I need to update this table in SQL Server with data from its  parent  table  see below   Table  sale  id  int  udid  int  assid  int    Table  ud  id   int  assid   int    sale assid contains the correct value to update ud assid    What query will do this  I m thinking of a join but I m not sure if it s possible

User · Answer

I was thinking the SQL-Server one in the top post would work for Sybase since they are both T-SQL but unfortunately not.

For Sybase I found the update needs to be on the table itself not the alias:

update ud
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

User · Answer

A standard SQL approach would be   UPDATE ud SET assid    SELECT assid FROM sale s WHERE ud id s id    On SQL Server you can use a join  UPDATE ud SET assid   s assid FROM ud u JOIN sale s ON u id s id

User · Answer

The simplest way is to use the Common Table Expression  CTE  introduced in SQL 2005  with cte as  select u assid col1  s assid col2 from ud u inner join sale s on u id   s udid  update cte set col1 col2

User · Answer

UPDATE tblAppraisalBasicData SET tblAppraisalBasicData ISCbo 1 FROM tblAppraisalBasicData SI INNER JOIN  aaa test RAN ON SI EmpID   RAN ID

User · Answer

PostgreSQL   CREATE TABLE ud  id integer  assid integer   CREATE TABLE sales  id integer  udid integer  assid integer    UPDATE ud SET assid   sales assid FROM sales WHERE sales id   ud id

User · Answer

MySQL  You ll get the best performance if you forget the where clause and place all conditions in the ON expression   I think this is because the query first has to join the tables then runs the where clause on that  so if you can reduce what is required to join then that s the fasted way to get the results do the udpate   Example  Scenario  You have a table of users  They can log in using their username or email or account number  These accounts can be active  1  or inactive  0   This table has 50000 rows  You then have a table of users to disable at one go because you find out they ve all done something bad  This table however  has one column with usernames  emails and account numbers mixed  It also has a  has run  indicator which needs to be set to 1  true  when it has been run  Query  UPDATE users User     INNER JOIN         blacklist users BlacklistUser         ON                       User username   BlacklistUser account ref             OR             User email   BlacklistedUser account ref             OR             User phone number   BlacklistUser account ref             AND             User is active   1             AND             BlacklistUser has run   0               SET         User is active   0          BlacklistUser has run   1    Reasoning  If we had to join on just the OR conditions it would essentially need to check each row 4 times to see if it should join  and potentially return a lot more rows  However  by giving it more conditions it can  skip  a lot of rows if they don t meet all the conditions when joining   Bonus  It s more readable  All the conditions are in one place and the rows to update are in one place

User · Answer

Simplified update query using JOIN-ing multiple tables      UPDATE         first table ft         JOIN second table st ON st some id   ft some id         JOIN third table tt  ON tt some id   st some id                   SET         ft some column   some value     WHERE ft some column   123456 AND st some column   123456   Note - first table  second table  third table and some column like 123456 are demo table names  column names and ids  Replace them with the valid names

User · Answer

postgres  UPDATE table1 SET    COLUMN   value FROM   table2         table3 WHERE  table1 column id   table2 id        AND table1 column id   table3 id        AND table1 COLUMN   value        AND table2 COLUMN   value        AND table3 COLUMN   value

User · Answer

This should work in SQL Server   update ud  set assid   sale assid from sale where sale udid   id

User · Answer

The following statement with FROM keyword is used to update multiple rows with a join   UPDATE users  set users DivisionId divisions DivisionId from divisions join users on divisions Name users Division

User · Answer

Teradata Aster offers another interesting way how to achieve the goal   MERGE INTO ud --what trable should be updated USING sale -- from what table relation update info should be taken ON ud id   sale udid --join condition WHEN MATCHED THEN      UPDATE SET ud assid   sale assid  -- how to update

User · Answer

And in MS ACCESS   UPDATE ud  INNER JOIN sale ON ud id   sale udid SET ud assid   sale assid

User · Answer

Syntax strictly depends on which SQL DBMS you re using  Here are some ways to do it in ANSI ISO  aka should work on any SQL DBMS   MySQL  SQL Server  and Oracle  Be advised that my suggested ANSI ISO method will typically be much slower than the other two methods  but if you re using a SQL DBMS other than MySQL  SQL Server  or Oracle  then it may be the only way to go  e g  if your SQL DBMS doesn t support MERGE    ANSI ISO   update ud       set assid               select sale assid            from sale            where sale udid   ud id         where exists         select          from sale        where sale udid   ud id       MySQL   update ud u inner join sale s on     u id   s udid set u assid   s assid   SQL Server   update u set u assid   s assid from ud u     inner join sale s on         u id   s udid   PostgreSQL   update ud   set assid   s assid from sale s  where ud id   s udid    Note that the target table must not be repeated in the FROM clause for Postgres   Oracle   update      select         u assid as new assid          s assid as old assid     from ud u         inner join sale s on             u id   s udid  up set up new assid   up old assid   SQLite   update ud       set assid               select sale assid            from sale            where sale udid   ud id         where RowID in         select RowID        from ud        where sale udid   ud id

User · Answer

Another example why SQL isn t really portable   For MySQL it would be   update ud  sale set ud assid   sale assid where sale udid   ud id    For more info read multiple table update  http   dev mysql com doc refman 5 0 en update html  UPDATE  LOW PRIORITY   IGNORE  table references     SET col name1  expr1 DEFAULT     col name2  expr2 DEFAULT            WHERE where condition

User · Answer

Try this one  I think this will works for you  update ud  set ud assid   sale assid  from ud   Inner join sale on ud id   sale udid  where sale udid is not null

User · Answer

For SQLite use the RowID property to make the update   update Table set column    NewValue  where RowID     select t1 RowID from Table t1 join Table2 t2 on t1 JoinField   t2 JoinField where t2 SelectValue    FooMyBarPlease

[sql] How can I do an UPDATE statement with JOIN in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005

Examples related to sql-update