[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-10-06
3
Medium Priority
?
406 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 1400 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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard 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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

611 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