By Ant Swift


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

I need to update this table in SQL Server 2005 with data from its 'parent' table, see below:

sale

id (int)
udid (int)
assid (int)

ud

id  (int)
assid  (int)

sale.assid contains the correct value to update ud.assid.

What query will do this? I'm thinking a join but I'm not sure if it's possible.

15 comments

@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 ud.id = 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.

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

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

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

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

@Prabakaran Raja 2014-09-10 15:25:45

Can you please explain for SQLite?

@Jeremy Phelps 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 ud.id 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...

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

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

@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

@Patrick_870206 2016-01-20 17:11:20

Thanks, this seems to me the most elegant solution for MSSQL.

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

And in MS ACCESS:

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

@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

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 

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

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

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.

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

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

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;

@Almog Cohen 2018-06-03 16:40:44

This is the only one to work for PostgreSQL

@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 = 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]

@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

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

@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 = ud.id. And not sale.id.

@Fabian Röling 2017-11-23 13:01:43

First one worked for me in Oracle SQL.

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

27 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

10 Answered Questions

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

16 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

28 Answered Questions

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

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

32 Answered Questions

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

38 Answered Questions

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

37 Answered Questions

9 Answered Questions

[SOLVED] SQL update query using joins

Sponsored Content