Let us say you are running an UPDATE statement on the table, however the information you are putting into this base table comes from another auxiliary table. Normally, you'd JOIN the information and never expect the rows within the UPDATE statement's FROM clause to multiply, maintaining that certain new row maps to 1 old row within the base table.
But I'm wondering an amount happen in case your JOIN table was ambiguous in some way, as if you could not take into account each base entity mapping simply to one became a member of entity. Or maybe you probably did something nonsensical like join to some base table to some table of their children and up-to-date the bottom table by using their information. Wouldso would it choose? Now you will find multiple rows per every base table row.
I went an argument such as this in SQL Server 2005 also it appeared to become selecting the very first row in every set. But that simply appears wrong in my experience. Should not it fire a mistake? Why preferred behavior?
-- normal -- categories are one-to-many bundles update bundles_denormalized set category = c.description from bundles_denormalized b left join categories c on b.category_id = c.id -- ambiguous -- bundles are one-to-many products update bundles_denormalized set category = p.description from bundles_denormalized b left join products p on b.id = p.bundle_id
Really basically comprehend the question properly it's upgrading the area multiple occasions, it is only that since there's just one record, therefore it eventually ends up with just one value. Why does not it error? Since the syntax is correct and also the database hasn't method of understanding what your intent was. Would for you to do this? Not normally, for this reason you need to perform a choose of the update prior to running it to guarantee the corret records are becoming the right values.
It's my job to write an update having a join by doing this:
update b set category = p.description --select b.category, p.description from bundles_denormalized b left join products p on b.id = p.bundle_id
I additionally could be cautious about utilizing a left participate in an update because you will could get values transformed to nulls. That's ok if that's that which you wanted, although not whether it is not.
Using UPDATE using the FROM Clause
The outcomes of the UPDATE statement are undefined when the statement features a FROM clause that's not specified by such a manner that just one value is readily available for each column occurrence that's up-to-date, that's when the UPDATE statement isn't deterministic. For instance, within the UPDATE statement within the following script, both rows in Table1 satisfy the qualifications from the FROM clause within the UPDATE statement but it's undefined which row from Table1 can be used to update the row in Table2.
USE AdventureWorks; GO IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1; GO IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL DROP TABLE dbo.Table2; GO CREATE TABLE dbo.Table1 (ColA int NOT NULL, ColB decimal(10,3) NOT NULL); GO CREATE TABLE dbo.Table2 (ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL); GO INSERT INTO dbo.Table1 VALUES(1, 10.0); INSERT INTO dbo.Table1 VALUES(1, 20.0); INSERT INTO dbo.Table2 VALUES(1, 0.0); GO UPDATE dbo.Table2 SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB FROM dbo.Table2 INNER JOIN dbo.Table1 ON (dbo.Table2.ColA = dbo.Table1.ColA); GO SELECT ColA, ColB FROM dbo.Table2;
Quite simply, it's a valid syntax also it won't throw a mistake or exception.
But simultaneously you can't make sure that the update value would be the first or even the last record out of your FROM clause because it is not defined.
I really just observed it does something sompletely stupid during my situation. My intent was to produce a single row choose, but apparently there have been replicates which i did not expect. Within this situation, it really joined a mixture of data in to the target row, picking some posts in the first source row plus some in the second source row.
I am confident Firebird throws the best if the attempt is built to make a move as ambiuous because this. However Firebird does not offer the (non-standard?) sntax update X from X join Y whatsoever...