Solved

SQL update query from existing table where 2 or more conditions exist

Posted on 2008-10-06
3
399 Views
Last Modified: 2008-10-08
HI, everyone i'm new to the forum and have a problem ive been struggling with for a few days, am really hoping someone can help me:

Basically im looking to update a table from an exisitng table where 2 or more condtions exists in both tables

Will give my example in the hope anyway:

Table 1
id      PID      Sub            udf3
4      8      MAT             C
4      8      ENG      A
4      17      MAT        B
3      8      MAT            A
3      8      ENG       A
3       17      ENG       B

Table 2
ID5      PID      Sub            dtxt
23      8      MAT            null
23      8      ENG      null
24      8      ENG      null
24      17      MAT            null
23      17      FRE            null

Now, what i trying to do is update table2 (only records with id5 = '23'  with the values from table1 where  id = '4' and PID and Sub match those from table 2

All other entries to be left as null

Resulting in a table that looks like this:

Table 2
ID5      PID      Sub            dtxt
23      8      MAT            C
23      8      ENG      A
24      8      ENG      null
24      17      MAT            null
23      17      MAT            B            


I have tried an update query along the lines of:

SELECT pid, sub, grade FROM table1 where id = '4'

UPDATE table2 SET pid=table1.pid where id5 = '23'
UPDATE table2 SET sub=table1.sub where id5 = '23'
UPDATE table2 SET dtxt=table1.udf3 where id5 = '23'

Have also tried:

update table2
set dtxt = (select ud3 from table1 where table1.pid = pupilid and table1.sub = sub and table1.id =4 and pcheckid =23)
where exists
(select ud3 from report where table1.pid = pupilid and table1.sub = sub and table1.id =4 and pcheckid =23)

which returns more than one value and therfore fails

But so far no joy, any suggestions greatfully appreciated as ive been grappling with this for 3 days now.

Thanks in advance to everyone
 
0
Comment
Question by:nazateam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
Binuth earned 350 total points
ID: 22648059

Update Table2
set dtxt = A.udf3
From Table1 A
Inner Join Table2 B On A.Pid= B.Pid and B.ID5 = 23 AND A.id = 4

Open in new window

0
 
LVL 7

Expert Comment

by:orcic
ID: 22648076
update table2
set dtxt = t1.udf3
 from table2 t2
  iner join table1 t1 on t1.PID=t2.PID and t1.SUB=t2.SUB
 where t1.ID='4' and t2.ID3='23'
0
 
LVL 7

Expert Comment

by:orcic
ID: 22648091
oops....
update table2
set dtxt = t1.udf3
 from table2 t2
  inner join table1 t1 on t1.PID=t2.PID and t1.SUB=t2.SUB
 where t1.ID='4' and t2.ID5='23'
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

756 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