Solved

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

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 express 24 46
Whats wrong in this query - Select * from tableA,tableA 11 58
SSRS 2013 - Finding Datasets/StoredProcedures 4 54
Find unused columns in a table 12 97
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

710 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