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.

Answer 1

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. Main question: How to do an update + join in PostgreSQL?

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
 );

SQLite 3.33 added support for an UPDATE + FROM syntax analogous to the PostgreSQL one:

update ud
  set assid = s.assid
from sale s 
where ud.id = s.udid;

Main question: Update with Join in SQLite

Answer 2

This should work in SQL Server:

update ud 
set assid = sale.assid
from sale
where sale.udid = id

Answer 3

PostgreSQL

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

Answer 4

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

Answer 5

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;

Answer 6

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.

Answer 7

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]

Answer 8

Teradata's Aster offers another interesting way how to achieve the goal:

MERGE INTO ud --what table 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

Answer 9

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

Answer 10

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 and 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 update.

Example

Scenario

You have a table of users. They can log in using their username or email or phone_number. These accounts can be active (1) or inactive (0). This table has 50000 rows

You then have a table of users to disable (blacklist) at one go because you find out they've all done something bad.

A script runs that checks for users in the blacklist_users table and disables them in the users table.

This blacklist_users table however, only has one column with usernames, emails and account numbers all mixed together.

The blacklist_users table also has a "has_run" indicator which needs to be set to 1 (true) when it has been run so it can be skipped in future queries.

So if you have a WHERE clause here, internally, the results are getting brought back in the join and then the WHERE clause is being queried against that dataset. Instead, we can move all the where clause conditions into the join, and internally, remove the second query completely.

Therefore, this is the most optimal query to avoid needless lookups of the users table...

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 (1 for email, 1 for phone_number, 1 for username, 1 for is_active) to see if it should join, and potentially return a lot more rows. However, by giving it more conditions it can "skip" every row we're not updating.

Bonus

It's more readable. All the conditions are in one place and the rows to update are in another place.

I hope all that makes sense.

Answer 11

The following statement with the 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

Answer 12

The simplest way is to use the Common Table Expression (CTE) introduced in SQL Server 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

Answer 13

And in Microsoft Access:

UPDATE ud
INNER JOIN sale ON ud.id = sale.udid
SET ud.assid = sale.assid;

Answer 14

Try this one. I think this will work 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

Answer 15

UPDATE tblAppraisalBasicData
SET tblAppraisalBasicData.ISCbo=1
FROM tblAppraisalBasicData SI INNER JOIN  aaa_test RAN ON SI.EmpID = RAN.ID

Answer 16

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');

Answer 17

For PrestaShop users who use MySQL 5.7:

UPDATE
    ps_stock_available sa
    INNER JOIN ps_shop s
        ON sa.id_shop = s.id_shop AND s.id_shop = 1
    INNER JOIN ps_order_detail od
        ON sa.id_product = od.product_id AND od.id_order = 22417
SET
    sa.physical_quantity = sa.quantity + sa.reserved_quantity

This was an example, but the point is as Eric said in How can I do an UPDATE statement with JOIN in SQL Server?.

You need to add an UPDATE statement at first with the full address of all tables to join with, and then add the SET statement.

Answer 18

To perform an UPDATE statement with a JOIN in SQL Server, you can use the JOIN syntax in combination with the UPDATE statement. Here's an example query that should update the ud table based on the corresponding values from the sale table:

UPDATE ud
SET ud.assid = sale.assid
FROM ud
JOIN sale ON ud.id = sale.udid;

In this query, the ud table is being updated, and the JOIN is performed between the ud and sale tables based on the matching id and udid columns, respectively. The SET clause specifies the column to be updated, ud.assid, and assigns it the value from the sale.assid column.

dbForge Studio'se SQL editor empowers you to execute your queries and proficiently manage your databases.

Answer 19

I had a similar situation where I needed to pick up and update user_id in orders from corporate_subscriptions in postgresql. The following query worked in my case:

update orders set user_id=cs_user_id from (select o.id, o.user_id as order_user_id, cs.id as cs_id, cs.user_id as cs_user_id from orders o right join corporate_subscriptions cs on cs.id=o.corporate_subscription_id where corporate_subscription_id is not null) o where orders.id=o.id and orders.source='Subscription';