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.
SQL Server UPDATE JOIN syntax In SQL Server, you can use these join clauses in the UPDATE statement to perform a cross-table update. In this syntax: First, specify the name of the table (t1) that you want to update in the UPDATE clause. Next, specify the new value for each column of the updated table.
SQL UPDATE JOIN could be used to update one table using another table and join condition.
You can't update two tables at once, but you can link an update into an insert using OUTPUT INTO , and you can use this output as a join for the second update: DECLARE @ids TABLE (id int); BEGIN TRANSACTION UPDATE Table1 SET Table1.
You can't issue an UPDATE statement using a group by. The point of using GROUP BY is to change the way that the result set is displayed to the user. When you have a GROUP BY statement you utilize the HAVING clause to filer the aggregated result set.
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 );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With