标签云

微信群

扫码加入我们

WeChat QR Code

I need to update this table in SQL Server 2005 with data from its 'parent' table, see below:saleid (int)udid (int)assid (int)udid(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.


What RDBMS are you using? MySQL, SQL Server, Oracle, PostgreSQL or something else?

2019年04月22日07分07秒

some relations between the tables? How can one know which record from sale corresponds to which record from ud? Is it based on id as primary key in both tables?

2019年04月22日07分07秒

How can you update UD? It only has the assid and it's own ID. Could you give an example in terms of actual values that exist, and the records you would like changed or added as a result of the script?

2019年04月22日07分07秒

See also SO question ... stackoverflow.com/questions/982919/sql-update-query-using-joins

2019年04月22日07分07秒

User Alias in query like stackoverflow.com/questions/982919/sql-update-query-using-joins

2019年04月22日07分07秒

It looks to me that the MySQL set assid = s.assid should be set u.assid = s.assid.

2019年04月22日07分07秒

In the ANSI syntax, what happens if the SELECT after the = returns more than one row?

2019年04月22日07分07秒

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.

2019年04月22日07分07秒

PrabakaranRaja, ANSI/ISO answer works for sqlite3.

2019年04月22日07分07秒

I wish the OP choose some better names for his table and columns!! it is not such readable/intuitive...

2019年04月22日07分07秒

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

2019年04月22日07分07秒

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?

2019年04月22日07分07秒

With the first one, you can't match on 2+ columns, but join works great.

2019年04月22日07分07秒

makciook: huh? You can just add more conditions in the WHERE clause if you want to match on additional columns.

2019年04月22日07分07秒

Just a nit... but I think the OP meant sale.udid = ud.id. And not sale.id.

2019年04月22日07分07秒

First one worked for me in Oracle SQL.

2019年04月22日07分07秒

This is the only one to work for PostgreSQL

2019年04月22日07分07秒

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

2019年04月22日07分07秒

Could you explain this a bit?

2019年04月22日07分07秒

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.

2019年04月22日07分07秒