March 23, 2017 at 10:37 am
hello, I need some help trying to figure this one out.
TableA
id
tablebid
TableB
id
TableA has 4512 rows and TableB has 4512 rows. I need to update TableA.tablebid with TableB.id.
I tried the following:
UPDATE TableA
SET TableA.tablebid = (SELECT id FROM TableB)
Error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I then tried:
UPDATE TableA
SET tablebid = t1.id
FROM TableB t1
WHERE t1.id IN
(SELECT t3.Id FROM TableB AS t3)
This loaded the top row id from TableB into each row of TableA.
March 23, 2017 at 10:59 am
wmbc - Thursday, March 23, 2017 10:37 AMhello, I need some help trying to figure this one out.TableA
id
tablebidTableB
idTableA has 4512 rows and TableB has 4512 rows. I need to update TableA.tablebid with TableB.id.
I tried the following:
UPDATE TableA
SET TableA.tablebid = (SELECT id FROM TableB)
Error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.I then tried:
UPDATE TableA
SET tablebid = t1.id
FROM TableB t1
WHERE t1.id IN
(SELECT t3.Id FROM TableB AS t3)
This loaded the top row id from TableB into each row of TableA.
Write a select which joins the two tables and returns TableA.tablebid and TableB.id. It should probably return 4512 rows.
Add in a few extra columns from both sides to provide checkable clues that it's working, then convert it to an update.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2017 at 11:14 am
i don't have a way to join the two tables as TableA.tablebid is null.
select tablebid,null as id from TableA
union
select null,id from TableB
results were like:
tablebid id
null null
null 123
null 456
The top row is returned as null in both columns - why? but i did get 4513 not 4512. how would i turn the above into an update without the invalid first row?
i figured out how to remove the null row with:
select tablebid,null as id from TableA WHERE tablebid IS NOT null
union
select null,id from TableB WHERE id IS NOT null
that returned 4512 rows
March 23, 2017 at 11:38 am
i tried this:
UPDATE TableA
SET tablebid = t1.id
FROM TableB t1
where t1.id IN (
SELECT NULL AS id FROM TableA WHERE tableid IS NOT null
UNION
SELECT id FROM TableB WHERE id IS NOT null
)
it updated all TableA rows but with only the first row from TableB not exactly the desired results
March 23, 2017 at 11:40 am
wmbc - Thursday, March 23, 2017 11:14 AMi don't have a way to join the two tables as TableA.tablebid is null.
select tablebid,null as id from TableA
union
select null,id from TableB
if you don't have a way to join the two tables, how will the UPDATE statement be able to decide which row from TableB to get the ID that should populate TableBID for which row in TableA?
March 23, 2017 at 11:45 am
wmbc - Thursday, March 23, 2017 11:38 AMi tried this:UPDATE TableA
SET tablebid = t1.id
FROM TableB t1
where t1.id IN (
SELECT NULL AS id FROM TableA WHERE tableid IS NOT null
UNION
SELECT id FROM TableB WHERE id IS NOT null
)it updated all TableA rows but with only the first row from TableB not exactly the desired results
What exactly are the desired results?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 23, 2017 at 12:12 pm
I'm trying to sequentially get the row of id's from TableB.id into TableA.tableid from row 1 to row 4512
TableB
id
123
456
end results to look like:
TableA
tablebid
123
456
March 23, 2017 at 12:21 pm
wmbc - Thursday, March 23, 2017 12:12 PMI'm trying to sequentially get the row of id's from TableB.id into TableA.tableid from row 1 to row 4512TableB
id
123
456end results to look like:
TableA
tablebid
123
456
Use ROW_NUMBER() to number the rows in table b in a CTE, then join to table a - either on table a id, or on a ROW_NUMBER over table a.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 23, 2017 at 12:31 pm
okay that went over my head:
Use ROW_NUMBER() to number the rows in table b in a CTE, then join to table a - either on table a id, or on a ROW_NUMBER over table a.
March 23, 2017 at 12:32 pm
; WITH a AS
(
SELECT id, tablebid, ROW_NUMBER() OVER(ORDER BY id) AS rn
FROM TableA
)
, b AS
(
SELECT tablebid, ROW_NUMBER() OVER(ORDER BY tablebid) AS rn
FROM TableB
)
UPDATE a
SET tablebid = b.tablebid
FROM a
INNER JOIN b
ON a.rn = b.rn
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2017 at 12:52 pm
thanks alot guys i really learned quite a bit and appreciate the help. drew.allen i was headed in that direction via chris' info
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply