Solved

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

Posted on 2008-10-06
3
395 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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

9 Experts available now in Live!

Get 1:1 Help Now