This should work in SQL Server:

update ud set assid = sale.assidfrom salewhere sale.udid = id

PostgreSQL

UPDATE table1SET COLUMN = valueFROM table2,table3WHERE table1.column_id = table2.idAND table1.column_id = table3.idAND table1.COLUMN = valueAND table2.COLUMN = valueAND table3.COLUMN = value

A standard SQL approach would be

UPDATE udSET assid = (SELECT assid FROM sale s WHERE ud.id=s.id)

On SQL Server you can use a join

UPDATE udSET assid = s.assidFROM ud uJOIN 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 udSET assid = sales.assidFROM salesWHERE sales.id = ud.id;

Simplified update query using JOIN-ing multiple tables.

 UPDATEfirst_table ftJOIN second_table st ON st.some_id = ft.some_idJOIN third_table tt ON tt.some_id = st.some_id.....SETft.some_column = some_valueWHERE 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, saleset ud.assid = sale.assidwhere 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_referencesSET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...[WHERE where_condition]

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

MERGE INTO ud --what table should be updatedUSING sale -- from what table/relation update info should be takenON ud.id = sale.udid --join conditionWHEN 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 udset u.assid = s.assidfrom ud uinner join sale s onu.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 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 UserINNER JOINblacklist_users BlacklistUserON(User.username = BlacklistUser.account_refORUser.email = BlacklistedUser.account_refORUser.phone_number = BlacklistUser.account_refANDUser.is_active = 1ANDBlacklistUser.has_run = 0)SETUser.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.

The following statement with the FROM keyword is used to update multiple rows with a join:

UPDATE usersset users.DivisionId=divisions.DivisionIdfrom divisions join users on divisions.Name=users.Division

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

And in Microsoft Access:

UPDATE udINNER JOIN sale ON ud.id = sale.udidSET ud.assid = sale.assid;

Try this one. I think this will work for you.

update udset ud.assid = sale.assidfrom udInner join sale on ud.id = sale.udidwhere sale.udid is not null

UPDATE tblAppraisalBasicDataSET tblAppraisalBasicData.ISCbo=1FROM 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 t1join Table2 t2 on t1.JoinField = t2.JoinFieldwhere t2.SelectValue = 'FooMyBarPlease');

For PrestaShop users who use MySQL 5.7:

UPDATEps_stock_available saINNER JOIN ps_shop sON sa.id_shop = s.id_shop AND s.id_shop = 1INNER JOIN ps_order_detail odON sa.id_product = od.product_id AND od.id_order = 22417SETsa.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.

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 udSET ud.assid = sale.assidFROM udJOIN 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.

You can perform an UPDATE statement with a JOIN in SQL Server to update records in one table based on data from another table. Here's the basic syntax for such an operation:

UPDATE target_tableSET target_column1 = source_column1,target_column2 = source_column2FROM source_tableWHERE target_table.key_column = source_table.key_column;

Here's a breakdown of the syntax:

• target_table: The table you want to update.
• SET: Specifies the columns in the target table that you want to update and the values to set them to.
• source_table: The table you want to join with for updating.
• WHERE: Specifies the condition for matching rows in both tables. This condition typically involves columns with the same values in both tables.

Here's a practical example. Let's say you have two tables, orders and order_updates, and you want to update the order_status column in the orders table based on data in the order_updates table where the order_id matches:

UPDATE ordersSET order_status = order_updates.new_statusFROM order_updatesWHERE orders.order_id = order_updates.order_id;

In this example, we're updating the order_status in the orders table with the new_status from the order_updates table where the order_id values match.

Make sure to be cautious when using UPDATE statements with JOIN as they can modify multiple records at once. Always test your queries on a backup or in a safe environment first, especially when working with production data.