update a table from another table

On Oct 18, 4:41 am, Daigoro wrote:
> Scriveva iccsi lunedì, 17/10/2011:
>
>
>
>
>
> > I want to update my table 1 from table2 and have following data
>
> > table1
>
> > MyID MyData1  MyData2
> >   1       A             C
> >   2       B             C
> >   3       H             F
>
> > table2
>
> >  MyID1   MyData3    MyData4
>
> >    1         G              C
> >    2         H               C
>
> > I want to update table1 all MyData2 are "C"   to MyData2 data.
> > I tried to look the UPDATE statement, but can not find anything to do
> > the update.
>
> I didn't understand the specific update you need; anyway, updating a
> table from another table works as follows:
> UPDATE table1 SET
> fieldTable1 = table2.fieldTable2
> FROM table2
> WHERE (join condition, e.g., table1.id = table2.fkID)- Hide quoted text -
>
> - Show quoted text -

Thanks a million for helping,


iccsi
 >> Stay informed about: update a table from another table 

iccsi ( ) writes:
> I want to update my table 1 from table2 and have following data
>
> table1
>
> MyID MyData1 MyData2
> 1 A C
> 2 B C
> 3 H F
>
>
> table2
>
> MyID1 MyData3 MyData4
>
> 1 G C
> 2 H C
>
> I want to update table1 all MyData2 are "C" to MyData2 data.
> I tried to look the UPDATE statement, but can not find anything to do
> the update.

I've been reading this a couple of times, but the description appears
to be incoherent.

The recommendation is that you post:
1) CREATE TABLE statements for your tables.
2) INSERT statements for your data.
3) The desired result given the sample.
4) A short narrative of the underlying business problme.
5) Which version of SQL Server you are using.

This makes it posible to post a tested solution, and it helps to
clarify what you are looking for.


--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 >> Stay informed about: update a table from another table 

Daigoro wrote:
> Scriveva iccsi lunedì, 17/10/2011:
>
>> I want to update my table 1 from table2 and have following data
>>
>> table1
>>
>> MyID MyData1 MyData2
>> 1 A C
>> 2 B C
>> 3 H F
>>
>>
>> table2
>>
>> MyID1 MyData3 MyData4
>>
>> 1 G C
>> 2 H C
>>
>> I want to update table1 all MyData2 are "C" to MyData2 data.
>> I tried to look the UPDATE statement, but can not find anything to do
>> the update.
>
> I didn't understand the specific update you need; anyway, updating a
> table from another table works as follows:
> UPDATE table1 SET
> fieldTable1 = table2.fieldTable2
> FROM table2
> WHERE (join condition, e.g., table1.id = table2.fkID)

No, that is not correct syntax. Both tables need to be mentioned in the FROM
clause, not just table2.

There are basically two choices for updates involving multiple tables:
subquery and UPDATE...FROM. The latter is frowned upon due to situations
where the result might not be predictable, but is often the better choice
when multiple columns are being updated, as long as precautions are taken to
make the result predictable - see below.

Subquery:
UPDATE table1
set col1=(select col2 from table2 where table1.keycol = table2.keycol)

Make sure the subquery returns a single row for any given keycol value.
Failure to do so will raise an error. If the potential for multiple matching
rows exists, use an aggregate function to guarantee a single row. For
example:
UPDATE table1
set col1=(select max(col2) from table2 where table1.keycol = table2.keycol)

UPDATE...FROM:
UPDATE t1
SET col1=t2.col2
FROM table1 as t1 join table2 as t2 ON t1.keycol=t2.keycol

The difference here is: if there are multiple rows in table2 that meet the
join criterion, no error will be raised - col1 will be set to one of the
col2 values in the matching rows, and there is no way to predict which value
it will be set to if multiple possiblities exist. You can mitigate this in a
similar way by using a derived table that groups by keycol:

UPDATE...FROM:
UPDATE t1
SET col1=t2.col2
FROM table1 as t1 join
(SELECT keycol, max(col2) as col2 FROM table2
group by keycol
) as t2 ON t1.keycol=t2.keycol
 >> Stay informed about: update a table from another table 

This should be a MERGE to prevent cardinality problems, but a VIEW
looks better than redundant data.
 >> Stay informed about: update a table from another table 

On Tue, 18 Oct 2011 17:28:12 -0400, "Bob Barrows"
wrote:

>> I didn't understand the specific update you need; anyway, updating a
>> table from another table works as follows:
>> UPDATE table1 SET
>> fieldTable1 = table2.fieldTable2
>> FROM table2
>> WHERE (join condition, e.g., table1.id = table2.fkID)
>
>No, that is not correct syntax. Both tables need to be mentioned in the FROM
>clause, not just table2.

Hi Bob,

As much as I dislike the syntax, it IS valid, even without the target
of the update in the FROM clause. If you paste the above code in
Management Studi, you'll see that it parses correctly. And if the
WHERE clause succeeds in matching each row in table1 to no more than
one row in table2, it will also execute the update as expected.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: update a table from another table 

Hugo Kornelis wrote:
> On Tue, 18 Oct 2011 17:28:12 -0400, "Bob Barrows"
> wrote:
>
>>> I didn't understand the specific update you need; anyway, updating a
>>> table from another table works as follows:
>>> UPDATE table1 SET
>>> fieldTable1 = table2.fieldTable2
>>> FROM table2
>>> WHERE (join condition, e.g., table1.id = table2.fkID)
>>
>> No, that is not correct syntax. Both tables need to be mentioned in
>> the FROM clause, not just table2.
>
> Hi Bob,
>
> As much as I dislike the syntax, it IS valid, even without the target
> of the update in the FROM clause. If you paste the above code in
> Management Studi, you'll see that it parses correctly. And if the
> WHERE clause succeeds in matching each row in table1 to no more than
> one row in table2, it will also execute the update as expected.

OK, I remember getting an error at one time from trying something like this.
Something else must have caused the error because I just tried it and you
are, of course, correct. Another case of fuzzy memory playing me false I
guess.
 >> Stay informed about: update a table from another table 

>> OK, I remember getting an error at one time from trying something like this. <<

/*
DROP TABLE OrderDetails, Orders;
CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
some_col DECIMAL (9,2) NOT NULL);

INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);

CREATE TABLE OrderDetails
(order_nbr INTEGER NOT NULL,
sku INTEGER NOT NULL,
item_price DECIMAL (9,2) NOT NULL,
PRIMARY KEY(order_nbr, sku),
-- FOREIGN KEY(sku) REFERENCES Products(sku)
FOREIGN KEY(order_nbr) REFERENCES Orders(order_nbr));

INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);

SELECT * FROM Orders;

UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;

results -- see item #1; last physical value
1 205.00 - where is the $500.00?
2 490.95
3 480.00
*/


--repeat with new physical ordering
DELETE FROM OrderDetails;
DELETE FROM Orders;
DROP INDEX OrderDetails.foobar;

-- index will change the execution plan
CREATE INDEX foobar ON OrderDetails (order_nbr, item_price);

INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);

INSERT INTO OrderDetails VALUES (1, 2, 205.00);
INSERT INTO OrderDetails VALUES (1, 1, 500.00);
INSERT INTO OrderDetails VALUES (2, 1, 490.95);
INSERT INTO OrderDetails VALUES (3, 1, 480.00);

UPDATE Orders
SET Orders.some_col = OrderDetails.item_price
FROM Orders
INNER JOIN
OrderDetails
ON Orders.order_nbr = OrderDetails.order_nbr;

SELECT * FROM Orders;

/*
Results
1 500.00
2 490.95
3 480.00
*/

What is the first property that you must have in an INDEX? It cannot
change the results of a statement, only the performance. See the
problem?

This would not have happened with the ANSI syntax. That's the point
that I am trying to make. The ANSI equivalent of the incorrect query
above is

UPDATE Orders -- no alias allowed!
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr)
WHERE EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr);

This will of course result in an error, and even the most junior of
junior programmers will eventually figure out (probably by asking a
senior) what's wrong. At that point, either the query is corrected to
match the request, or a note is sent back to management asking for a
clarification of the ambiguity in the request.

Bottom line: UPDATE FROM *can* be safely used - but only if you are
FOREVER certain that no single row in the target table can EVER be
joined to more than one row in the source table(s); FOREVER in the
ENTIRE LIFETIME of the application; FOREVER across all programmers yet
to come.

I like to err on the safe side, I do not bet only an endless stream of
100% perfect programmers. Use MERGE and get a cardianlity violation
error.
 >> Stay informed about: update a table from another table