0

I have these tables:

tblPart

ID, field1, field2, ecc

tblUserFreeProperty

ID_tblPart, ID, identname, val

I need to write a query to update tblUserFreeProperty, but only the val filed when tblPart.ID = tblUserFreeProperty.ID and identname has a specific value.

In the database, identname has one of 8 possible values, and I need to update all of them.

I have wrote a query for only one value at time:

UPDATE tblUserFreeProperty
SET val = N'??_??@True;'
FROM tblUserFreeProperty 
LEFT OUTER JOIN tblPart ON tblUserFreeProperty.id = tblPart.id
WHERE tblUserFreeProperty.id  = N'3' 
  AND (tblUserFreeProperty.identname = N'DSR_Mag.Gestito')

Is there a way to write the update query for all 8 different values at the same time?

Added explanation: I need a query like this:

UPDATE tblUserFreeProperty
SET val1 = N'??_??@True;, val2 = N'??_??@False;', val3 = N'5', val4 = N'BK',...val8
FROM tblUserFreeProperty 
LEFT OUTER JOIN tblPart ON tblUserFreeProperty.id = tblPart.id
WHERE (tblUserFreeProperty.id = N'3' AND (tblUserFreeProperty.identname = N'DSR_Mag.Gestito'))->SET Val1
   OR (tblUserFreeProperty.id = N'3' AND (tblUserFreeProperty.identname = N'DSR_Mag.Done'))->SET Val2
  OR (tblUserFreeProperty.id = N'3' AND (tblUserFreeProperty.identname = N'Something Else'))->SET val3

I hope it is more clear

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gianfranco Vrech
  • 43
  • 9
  • What do you mean by you need to "upgrade" the value? – Thom A Oct 21 '22 at 13:32
  • If you want to use multiple different values for `identname`, why not use an `IN` clause? – Thom A Oct 21 '22 at 13:33
  • 1
    I think what you are after is a CASE WHEN ... THEN ... WHEN ... THEN ... ELSE .... END statement See: https://stackoverflow.com/questions/15766102/i-want-to-use-case-statement-to-update-some-records-in-sql-server-2005 – apc Oct 21 '22 at 13:34
  • In the tblUserFreeProperty I have 8 different values. I need to upgrade all of them. In this moment with the SET I'm ok with one, but If I need to change this "tblUserFreeProperty.identname = N'DSR_Mag.Gestito'" I have to rewrite the query. – Gianfranco Vrech Oct 21 '22 at 13:35
  • *"I need to upgrade all of them."* What does "upgrading" them mean? You haven't clarified this. – Thom A Oct 21 '22 at 13:42

3 Answers3

0

You haven't explained your problem very well but hopefully this helps

UPDATE tblUserFreeProperty
SET    val = N'??_??@True;', anotherColumn = 5, anotherOne = 'Something'
FROM   tblUserFreeProperty LEFT OUTER JOIN
              tblPart ON tblUserFreeProperty.id = tblPart.id
WHERE         tblUserFreeProperty.id  = N'3' 
and (tblUserFreeProperty.identname = N'DSR_Mag.Gestito')

EDIT: Adding the explanation and code below with another solution to the question which might be better suited, even though I still don't truly understand the question.

Here's how it'd look with more than one condition which you can add with OR

UPDATE tblUserFreeProperty
SET    val = N'??_??@True;'
FROM   tblUserFreeProperty LEFT OUTER JOIN
              tblPart ON tblUserFreeProperty.id = tblPart.id
WHERE         (tblUserFreeProperty.id  = N'3' 
and (tblUserFreeProperty.identname = N'DSR_Mag.Gestito')) OR (tblUserFreeProperty.id  = N'45435' 
and (tblUserFreeProperty.identname = N'Something')) OR (tblUserFreeProperty.id  = N'394290' 
and (tblUserFreeProperty.identname = N'Something Else'))

The way OR works is very simple. It checks if the first brackets are TRUE, if not then the second, if not the third. If ANY are TRUE then it does the UPDATE, if not then obviously it doesn't.

Ineffable21
  • 153
  • 1
  • 13
  • I'm sorry for my explanation. Your replay solve just a case. I know I can set more then one value, but the problem is the Where statement. If tblUserFreeProperty.identname = Val1 this works. But I have 8 different Where. The colums in the tblUserFreeProperty are only two with the values i need to change. – Gianfranco Vrech Oct 21 '22 at 13:40
  • If you need it to happen for different conditions then just add some with OR. Look at the change I'm gonna make to my answer – Ineffable21 Oct 21 '22 at 13:41
  • The Where condition with the or it's ok, but the problem is also to set a different value in the right condition. – Gianfranco Vrech Oct 21 '22 at 13:54
  • So you want to set 8 different values on 8 different conditions? For example if someone is 8 years old you want to change their age to 'Eight' if they are 9 years old you want to change it to 'nine' if 10 to 'ten' and so on? Or am I not understanding? – Ineffable21 Oct 21 '22 at 13:58
  • I have make a modification in my original question for better explain the problem. – Gianfranco Vrech Oct 21 '22 at 14:03
0

Though I could not get exactly what is required from your question. Please check if the below could serve the purpose of replacing the eight-string with the required values.

UPDATE       tblUserFreeProperty
SET           val =  '??_??@True;'
where  id IN (SELECT tblPart.id FROM tblPart) 
and  identname IN ('1', '2', '3', '4', '5', '6', '7', '8')
sankar
  • 189
  • 1
  • 10
0

I can't set differtent values of one column in the same time... https://stackoverflow.com/questions/24784653/sql-multi-set-with-one-where[enter

I findout the answer.

Gianfranco Vrech
  • 43
  • 9
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 25 '22 at 05:09