two - How can I do an UPDATE statement with JOIN in SQL?




update with join postgres (10)

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.


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


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;

And in MS ACCESS:

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

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]

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

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.


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

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

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 

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




sql-update