SQL Server - Insert XML to every row in a column

Question

I have 2 tables

Customer (CustomerID int, Name varchar(20), OrderHistory xml)
Order (OrderID int, CustomerID int, OrderDate date)

And I would like to insert into every row in OrderHistory column, an OrderDate of that Customer based on CustomerID.

Here is my query:

UPDATE Customer
SET OrderHistory = (SELECT OrderDate
                    FROM Order
                    WHERE CustomerID = 1
                    FOR XML AUTO)
WHERE CustomerID = 1

However, I have to change CustomerID for every new customer. Is there any way to insert into every customer at once ?

har07 · Accepted Answer · 2016-12-11 13:13:03Z

2

Applying UPDATE ... FROM ... construct to your initial SQL to make it update all customer data at once should be straightforward :

UPDATE Customer
SET OrderHistory = 
    (SELECT OrderDate
    FROM Order o
    WHERE o.CustomerID = c.CustomerID
    FOR XML AUTO)
FROM Customer c

edited Dec 11, 2016 at 13:13

answered Dec 11, 2016 at 12:45

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ahmed abdelqader · Accepted Answer · 2016-12-11 11:57:41Z

0

For Updating table via using Join another table use the next Approach:

UPDATE A
SET foo = B.bar
FROM TableA A
JOIN TableB B
ON A.col1 = B.colx
WHERE ...

So the update query will be as next:

UPDATE A
SET OrderHistory = B.OrderDate
FROM Customer A
JOIN Order B
ON A.CustomerID = B.CustomerID

answered Dec 11, 2016 at 11:57

ahmed abdelqader

3,58220 silver badges38 bronze badges

Comments

Chương Trương · Accepted Answer · 2016-12-11 12:32:43Z

-1

I just figured out:

DECLARE
@count int
SET
@count = 0
UPDATE Customer
WHILE @count < (SELECT COUNT (*) FROM Customer)
BEGIN
SET OrderHistory = (SELECT
    OrderDate
FROM
    Order
WHERE CustomerID = @count
FOR XML AUTO)
WHERE CustomerID = @count
SET @count = @count + 1
END

answered Dec 11, 2016 at 12:32

Chương Trương

132 bronze badges

1 Comment

Nick.Mc Over a year ago

Slow because you're running a select count in a loop, assumes that customer number has no gaps, and lastly unnecessarily uses a loop. There are many more far superior suggestions here - it would be a good idea to learn from them.

Collectives™ on Stack Overflow

SQL Server - Insert XML to every row in a column

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related