How to update via CTE

[Frank]

Hi,

I'm struggling to perform an update on a table using a CTE.
Here's a simple example (no keys etc) of what I'm trying to
accomplish:

-- Table 1
create table t1
(
id int,
datacol1 int,
datacol2 char(1)
);
go
insert into t1(id, datacol1, datacol2) values(1, 1, 'a');
insert into t1(id, datacol1, datacol2) values(1, 2, 'b');
insert into t1(id, datacol1, datacol2) values(1, 3, 'c');

-- Table 2
create table t2
(
id int identity(1,1) not null primary key,
datacol1 char(1),
datacol2 char(1),
datacol3 char(1),
datacol4 int,
datacol5 int,
datacol6 int
);
go
insert into t2(datacol1, datacol2, datacol3, datacol4, datacol5,
datacol6)
values('d', 'e', 'f', 4, 5, 6);

I want to flatten a selection of the t1 rows into a single row based
on the id and then update t2
(which looks like the flattened t1) using those values. So t2 should
end up with values (1, 2, 3, 'a', 'b', 'c')
instead of (4, 5, 6, 'd', 'e', 'f')

I can flatten the t1 rows using a an old-style pivot like this:

with cte(id, datacol_1, datacol_2, datacol_3, datacol_4, datacol_5,
datacol_6)
as
(
select id
, datacol_1 = (case when row_number() over(order by id) = 1 then
datacol1 end)
, datacol_2 = (case when row_number() over(order by id) = 2 then
datacol1 end)
, datacol_3 = (case when row_number() over(order by id) = 3 then
datacol1 end)
, datacol_4 = (case when row_number() over(order by id) = 1 then
datacol2 end)
, datacol_5 = (case when row_number() over(order by id) = 2 then
datacol2 end)
, datacol_6 = (case when row_number() over(order by id) = 3 then
datacol2 end)
from t1
)
select MAX(datacol_1), MAX(datacol_2), MAX(datacol_3), MAX(datacol_4),
MAX(datacol_5), MAX(datacol_6)
from cte group by id;

(As an aside, is there a better way of pivoting than using the MAX
statement above? I couldn't
get the GROUP BY to work without it, but it doesn't seem very elegant)

But I can't seem to find the correct syntax to update table t2 (either
directly through an update
statement on the table or via another CTE on t2).

I've tried:

with cte_t1(...)
as
(
-- select cols from t1
...
),
cte_2(...)
as
(
-- select cols from t2
...
)
update cte_t2
set col1 = value from cte_t1 etc

I've also tried:

with cte_t1(...)
as
(
-- select cols from t1
...
)
update t2 set col1 = value from cte_t1 etc

But I keep getting errors about invalid column names.
Can someone point me in the right direction please?
Or is there a better way of doing what I'm trying to achieve?

Many thanks,
Frank.

[Uri Dimant]

Frank

with cte
as
(
select trg.col as target_col,src.col as source_col
from t1 join t2 on t1.id=t2.id
) update cte set target_col=source_col

Sorry , cannot tested right now

"Frank" <franci...@gmail.com> wrote in message
news:a9331247-213e-438c...@v37g2000vbb.googlegroups.com...

[Plamen Ratchev]

Since you have pivoting on two columns using CASE expressions is probably the best. This should do it:

WITH Ranked AS (
SELECT id, datacol1, datacol2,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY datacol1) AS rk
FROM t1),
Pivoted AS (
SELECT id,
MAX(CASE WHEN rk = 1 THEN datacol2 END) AS datacol1,
MAX(CASE WHEN rk = 2 THEN datacol2 END) AS datacol2,
MAX(CASE WHEN rk = 3 THEN datacol2 END) AS datacol3,
MAX(CASE WHEN rk = 1 THEN datacol1 END) AS datacol4,
MAX(CASE WHEN rk = 2 THEN datacol1 END) AS datacol5,
MAX(CASE WHEN rk = 3 THEN datacol1 END) AS datacol6
FROM Ranked
GROUP BY id),
UpdateCTE AS (
SELECT A.datacol1, B.datacol1 AS datacol1_new,
A.datacol2, B.datacol2 AS datacol2_new,
A.datacol3, B.datacol3 AS datacol3_new,
A.datacol4, B.datacol4 AS datacol4_new,
A.datacol5, B.datacol5 AS datacol5_new,
A.datacol6, B.datacol6 AS datacol6_new
FROM t2 AS A
JOIN Pivoted AS B
ON A.id = B.id)
UPDATE UpdateCTE
SET datacol1 = datacol1_new,
datacol2 = datacol2_new,
datacol3 = datacol3_new,
datacol4 = datacol4_new,
datacol5 = datacol5_new,
datacol6 = datacol6_new;

--
Plamen Ratchev
http://www.SQLStudio.com

[Frank]

Hi,

Many thanks to both of you for your responses.
Both helped a lot.

Cheers,
Frank.