I have 2 tables. Both have the same amount of rows.
Sample data:
Table1{
Id, IdTable2Row
}
Table2 {
Id, RedChicken -- LOL
}
Each row of Table1 (column IdTable2Row) should get the id of 1 row of table 2 (irrelevant which one). Table1.IdTable2Row is null in every row before the operation.
How can I do something like this?
UPDATE Table1 SET IdTable2Row = (SELECT Id FROM [Table2])
update T1
set idtablerow = t2.id
from Table1 T1
inner join Table2 T2
on T1.something = T2.something
In your case, with no common column:
with T1 as
(
select Table1.*, row_number() over (order by anycolumn) rn
from Table1
)
update T1
set idtablerow = t2.id
from T1
inner join
(
select x1.*, row_number() over (order by id) rn
from Table2 x1
) T2
on t1.rn = t2.rn
Should work
If I understand correctly you need to match rows 1 to 1. Here is a solution I come up with:
declare @Table1 table(
Id INT,
IdTable2Row INT NULL
)
declare @Table2 table(
Id INT, RedChicken varchar(10) -- LOL
)
INSERT INTO @Table1
VALUES
(
1,NULL
),(
2,NULL
),(
3,NULL
);
INSERT INTO @Table2
VALUES
(
10,'s'
),(
20,'a'
),(
30,'b'
);
SELECT *
FROM @Table1 ;
SELECT *
FROM @Table2;
WITH cte1 AS
(
SELECT
ROW_NUMBER() over(ORDER by Id) AS rown
,Id
,IdTable2Row
FROM @Table1
),
cte2 AS
(
SELECT ROW_NUMBER() over(ORDER by Id) AS rown
,Id
,RedChicken
FROM @Table2
)
UPDATE t
SET IdTable2Row = c2.Id
FROM @Table1 t
JOIN cte1 c1 ON c1.Id = t.Id
JOIN cte2 c2 ON c1.rown = c2.rown
SELECT *
FROM @Table1
IN SQL SERVER if there is common column then you can join the table and update
update a set a.IdTable2Row=b.Id from
Table1 a ,Table2 b
where a.column=b.column
