Solved

SQL update problem

Posted on 2011-09-12
3
307 Views
Last Modified: 2012-05-12
I'm having a trouble with a simple updating statement.

I have two tables:
Table1 columns -
id1
itemId
reporterName
other
...

Table2 columns -
id2
itemId
firstName
lastName
otherfld
...

itemId in the two tables mean the same field. I need to select firstName and lastName from Table2 and insert "firstName, lastName " in Table1.

Here's my quesry -

update dbo.Table1
set reporterName=(
      select nn.lastName + ', ' + nn.firstName
      from dbo.Table2 nn inner join dbo.Table1 t on t.itemId=nn.itemId )
where itemId in (select rn.itemId from Table2 rn inner join dbo.Table1 tt on tt.itemId=rn.itemId)
and reporterName is null

I got the 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.

Please help. Thanks.
0
Comment
Question by:minglelinch
3 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 250 total points
ID: 36526038

Hi,

I think this is what you looking for

update Table1
set reporterName=t2.lastName + ', ' + t2.firstName
from Table1 t1
join Table2 t2 on t1.itemId=t2.itemId
where t1.reporterName is null


/peter
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 250 total points
ID: 36526538
try
update Table1
set ReportedName := ISNULL(t2.lastName + ', ', '',) + ISNULL(t2.firstName, '')
from Table2
inner join Table1 on (Table1.itemId = Table2.ItemID)
and Table1.reporterName is null

Open in new window

0
 
LVL 1

Author Closing Comment

by:minglelinch
ID: 36529178
Nice solution. Thank you both.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now