By Ant Swift

2009-08-18 11:40:07 8 Comments

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.


@Eric 2009-08-18 11:44:17

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


update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid =
 where exists (
      select * 
      from sale 
      where sale.udid =


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

SQL Server:

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


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

Note that the target table must not be repeated in the FROM clause for Postgres.


        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
   = s.udid) up
set up.new_assid = up.old_assid


update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid =
 where RowID in (
      select RowID 
      from ud 
      where sale.udid =

@dotancohen 2012-04-17 21:44:51

It looks to me that the MySQL set assid = s.assid should be set u.assid = s.assid.

@Throw Away Account 2017-05-05 17:49:26

In the ANSI syntax, what happens if the SELECT after the = returns more than one row?

@Francis Lord 2017-05-16 16:31:38

@ThrowawayAccount3Million It would probably fail. AFAIK, this kind of operation would expect a scalar value and will throw an error if given a result set instead.

@toto_tico 2018-05-11 11:36:04

@PrabakaranRaja, ANSI/ISO answer works for sqlite3.

@user2256825 2018-07-24 17:26:22

How to join 4 tables and update assuming sale.udid iis not equal to and have to join 3 more tables ?

@S.Serpooshan 2018-12-31 12:07:25

I wish the OP choose some better names for his table and columns!! it is not such readable/intuitive...

@StackUnder 2019-05-15 15:47:32

Postgre 9.3 worked only using update ud set assid = s.assid

@Kemal AL GAZZAH 2019-04-23 17:07:19

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 = s.udid)
update cte set col1=col2

@HARSHIT RATHORE 2018-11-23 13:42:57

Try this one, I think this will works for you

update ud

set ud.assid = sale.assid

from ud 

Inner join sale on = sale.udid

where sale.udid is not null

@KeithTheBiped 2018-05-13 14:45:29

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

@Mohammed Noureldin 2018-10-08 13:29:06

Could you explain this a bit?

@KeithTheBiped 2018-10-13 16:16:24

@MohammedNoureldin I'll try to explain. The problem is how to update a table with a result from a query on a Join using the same table. The (sub-select) statement acts like a join and returns a system field, RowID, which is a unique number for each row in a table. Since the sub-select can return multiple rows the "where RowID =" selects a single correct row from the resulting sub-select and does the update to the column. Let me know if you need more clarification or need to figure out a variation on this theme.

@Luke Watts 2018-02-09 16:57:54


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.



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


UPDATE users User
        blacklist_users BlacklistUser
            User.username = BlacklistUser.account_ref
   = BlacklistedUser.account_ref
            User.phone_number = BlacklistUser.account_ref
            User.is_active = 1
            BlacklistUser.has_run = 0
        User.is_active = 0,
        BlacklistUser.has_run = 1;


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.


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

@edosoft 2009-08-18 11:42:17

This should work in SQL Server:

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

@Richard 2017-03-07 18:38:28


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

@Dodecaphone 2019-05-29 14:50:10

As a caution, the SET must come immediately after the recordset definition! I've just been trying to work out a similar scenario in an Access database, which needed a WHERE clause (it would not accept it as a valid ON condition). WHERE had to come last to avoid syntax errors.

@Abdullah Yousuf 2016-12-19 11:38:30

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

@Sheryar Nizar 2016-04-06 11:34:28

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

@user1154043 2012-01-17 13:53:56


UPDATE table1
SET    COLUMN = value
FROM   table2,
WHERE  table1.column_id =
       AND table1.column_id =
       AND table1.COLUMN = value
       AND table2.COLUMN = value
       AND table3.COLUMN = value 

@alfonx 2014-03-07 21:28:31

The answer would be more handy if it would use the table/column names used in the question. Why are there 3 tables in your answer?

@Ken Goh 2014-11-19 08:24:18

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 = s.udid

@Vinit Kadkol 2014-07-14 07:15:25

Simplified update query using JOIN-ing multiple tables.

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

@xhudik 2014-07-03 10:22:18

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 = sale.udid --join condition
    UPDATE SET ud.assid = sale.assid; -- how to update

@alfonx 2014-03-07 21:35:53


CREATE TABLE ud (id integer, assid integer);
CREATE TABLE sales (id integer, udid integer, assid integer);

SET assid = sales.assid
FROM sales

@Yada 2009-11-16 19:17:16

Another example why SQL isn't really portable.

For MySQL it would be:

update ud, sale
set ud.assid = sale.assid
where sale.udid =;

For more info read multiple table update:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

@Jeff Moden 2013-01-26 00:55:08

+1 on the "why SQL isn't really portable" comment! Portability is so fragile that just declaring a variable will break portability among many of the popular database engines.

@MattH 2009-08-18 11:44:47

A standard SQL approach would be

SET assid = (SELECT assid FROM sale s WHERE

On SQL Server you can use a join

SET assid = s.assid
FROM ud u
JOIN sale s ON

@makciook 2013-07-11 08:28:01

With the first one, you can't match on 2+ columns, but join works great.

@siride 2015-07-11 05:32:17

@makciook: huh? You can just add more conditions in the WHERE clause if you want to match on additional columns.

@Skippy VonDrake 2017-01-14 18:40:31

Just a nit... but I think the OP meant sale.udid = And not

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] How to Join to first row

40 Answered Questions

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

13 Answered Questions

[SOLVED] Altering a column: null to not null

11 Answered Questions

[SOLVED] Update a table using JOIN in SQL Server?

34 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

28 Answered Questions

[SOLVED] Check if table exists in SQL Server

43 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

30 Answered Questions

12 Answered Questions


Sponsored Content