Conditional update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conditional update

Author

Topic

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-16 : 07:22:44

Hi Guys
I would like to update/create a table based on the contents of fields in another table. For example

Table1
Serial A1 A2 A3 A4
12345 1 0 1 0

Table2 P1 P2 P3 P4
Blank Blank Blank

Then say,
if Table1.A1=1 OR Table1.A2=1, make table2.P1=1
if Table1.A3=1 OR Table1.A4=1, make table2.P2=1
etc and so on!
Hope that make sense and thanks in advance for your help!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 07:27:44

Post some sample data and output. Format your post to make it readible. Refer this,

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-16 : 09:17:19

Also..whats is the relation between table1 and table2? Which record should it update?

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-16 : 09:39:41

Sorry, that's not very clear! I want to update by serial.
So, using the original example, after the update, Table 2 would look like this...

Table2
Serial P1 P2 P3
12345 1 0 0

Does that make any more sense?!

I'm very very new to SQL so I am not sure yet about the correct terminology!!!

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-16 : 09:44:32

Ok..looks like you need to insert data into Table 2 from Table 1. Can you try this..

INSERT INTO Table2
(Serial,P1,P2)
SELECT Serial,
CASE WHEN A1 = 1 OR A2 = 1 THEN 1 ELSE 0 END AS P1,
CASE WHEN A3 = 1 OR A4 = 1 THEN 1 ELSE 0 END AS P2
FROM Table1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 10:34:32

or is it this?


UPDATE t2
SET t2.P1= t1.A1 | t1.A2,
t2.P2=t1.A3 | t1.A4
..
FROM Table1 t1
JOIN Table2 t2
ON t2.Serial=t1.Serial

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-16 : 10:59:12

Hi guys

I think Vijay's method works. I assume that "CASE" works in a similar way to "IF".
The only thing is I have approx 250 columns in table1 to update 228 "P" columns in table 2 so I am in for a long afternoon's typing!!

Thank you so much for your help!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 11:06:12

quote:
Originally posted by paull

Hi guys

I think Vijay's method works. I assume that "CASE" works in a similar way to "IF".
The only thing is I have approx 250 columns in table1 to update 228 "P" columns in table 2 so I am in for a long afternoon's typing!!

Thank you so much for your help!!!

vijays method is not actually doing update but it is infact inserting new records to table.

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-16 : 11:15:41

I dont think it really matters if I insert or update. I can either start off with a blank template or shell and insert the data or start with a data set where each column is set to "0" and update.
Excuse me for going off topic, but the final parts of the table involve me counting the data, so PTOTAL will be the sum of all the "1"s in the previous columns. Am I heading in the right lines looking at SUM?

Thank you!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-16 : 11:29:17

Yes..SUM would work...but I must ask ...why do you need another table with this data? Why dont you operate on the same Table1 for your purpose?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 11:32:53

sorry...i dont understand you. how will insert and update be equivalent? if what you need is to get summarised figures in your table, then you should be doing update rather than insert as insert will simply go on adding detail rows which are existing in table. I'm not in a position to suggest much as i dont know your exact scenario. It would be better if you could post some data and then explain what you want as output from them so that we can understand what you're looking at.

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-16 : 11:39:46

Its quite an unusual task - therefore nearly impossible to explain!! How do I post data? I have a spreadsheet that might explain it!!

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-16 : 11:48:13

Vijay - It is survey data that we receive from an external source and my aim is to simply put this data into a table as it comes in. The second table is a summary if you like, which we use to calculate scores, indexes etc. We calculate each score from a combination of variables in the original data so Q1 or Q2 or Q3 = P1. Q4 or Q5 = P2, Q6 = P3, Q7 or Q8 = P4 etc etc.
Does that make any more sense??!!

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-16 : 12:08:04

Hi Vijay

In your example, how would I alter the code to pick up the data from multiple tables? In the example the last line is "FROM Table1"

The data I am checking against is in 3 tables. I tried From Table1 and Table2 and Table 3 but this would not compile. Any suggestions?!

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 12:35:25

quote:
Originally posted by paull

Hi Vijay

In your example, how would I alter the code to pick up the data from multiple tables? In the example the last line is "FROM Table1"

The data I am checking against is in 3 tables. I tried From Table1 and Table2 and Table 3 but this would not compile. Any suggestions?!

Many thanks

FRom Table1 t1
JOIN Table2 t2
ON t2.linkingcol=t1.linkingcol
JOIN Table3 t3
ON t3.LinkingCol=t2.LinkingCol

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-16 : 12:50:24

Thank you so much Visakh and Vijay, that now works a treat!!

Now onto learning the SUM bit!!!

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 13:59:18

welcome

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-17 : 07:47:14

I have encountered a problem!!!!

Using CASE WHEN works fine as in most instances the data is either 1 or 0. However one of the fields can be a 2!
Is it possble to do 2 "THEN"s before the ELSE, and if so, how!! I keep getting errors

Thanks

paull
Yak Posting Veteran

50 Posts

Posted - 2009-03-17 : 10:01:21

Sorry to be a pain guys, but any ideas on this?

CASE WHEN Q15_T22 =1 THEN 1 ELSE 0 END AS PP226, works fine, but I cant do 2 elses i.e.
CASE WHEN Q15_P99 =1 THEN 1 ELSE Q15_P99 =2 THEN 2 ELSE 0 END AS PP228,

Also, I cant put them on separate lines as I get the error
"The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."

Any suggestions would be more than welcome!!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-17 : 10:02:11

Yes ...u cna use nested CASE like below.

CASE WHEN A1 = 1 OR A2 = 1
THEN 1
ELSE CASE WHEN A10 = 2
THEN 1
ELSE 0
ENDEND

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-17 : 10:05:12

ok..I must have posted when u were giving ur sample data i guess..for ur case..this shud be ur CASE statement

CASE WHEN Q15_P99 =1 THEN 1 ELSE CASE WHEN Q15_P99 =2 THEN 1 ELSE 0 END END AS PP228

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources