SQL query for update a table with different values in the same column-CodePudding

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

CodePudding user response：

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')

CodePudding user response：

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.

CodePudding user response：

Maybe i have discovered I'm stupid... 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.