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.

16 Answers

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

This should work in SQL Server:

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

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 

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 

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; 

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.

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:

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

Teradata 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 

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 

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

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 

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 

And in MS ACCESS:

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

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 

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

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