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

Posted on 2008-10-06
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 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 = pupilid and table1.sub = sub and =4 and pcheckid =23)
where exists
(select ud3 from report where = pupilid and table1.sub = sub and =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
Question by:nazateam
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
LVL 14

Accepted Solution

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 = 4

Open in new window


Expert Comment

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'

Expert Comment

ID: 22648091
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'

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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…
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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

622 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