Link to home
Start Free TrialLog in
Avatar of Jase Alexander
Jase AlexanderFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Server Mass update of table records with multiple criteria

HI Experts

Hope you can help


I have a SQL table I need to update and wondering if I can update 80 rows - all with unique record ID's - in one query ?


I have attached the file in question - I need to update the Attribute Value in the file based on Link Field 1 so as an example - and please excuse my primitiveness in trying to outline my request :-


Where

[Link Field 1] = 'CI3080' THEN [Attribute Value] = '1000',

[Link Field 1] = 'IE3054' THEN [Attribute Value] = '2000'


And so on


Is this a CASE query or Function ?


Here is the front end of the query:-


select * from [LIVEBusinessLtd$Attribute Value] where [Attribute Code] = 'REPLN'

Order by [Numeric Value] Asc


This produces the list in the attachment


Any suggestions or assistance would be very welcome


Regards
JExample consolidated Update.xlsx


ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To add some color to ste5an's excellent comment about object naming..
  • Lose the special characters like $, everything except underscore _, as that forces code to use square brackets [ ] to surround the object name.
  • Lose the spaces, same.
  • Lose the SQL Server reserved words like timestamp and sequence, same.

and
  • Link Field 1, 2, 3 - Consider having a one-to-many table with LinkField, and if there are three there will be three rows in it, instead of the flattened three-column 1, 2, 3 as that will require some kind of UNION whenever you want to select a list of all link fields.
  • fyi the query has column [Numeric Value] but I don't see that in your table.
Avatar of Jase Alexander

ASKER

HI

Thank you for the above - thats great - also just to elaborate this is business -created columns and table mapping - all done third party at the point of introducing our MRP system

Apologies as this table has many values depending on the Attribute Code

Is there a way to insert and AND command into the above - I just want the Attribute Values to update on any of the location codes against the attrribute code of REPLN - I have an example of just one of the locations [Link Field 1] attached - I dont want to run the above and ALL of the attribute codes get assigned 1000 - Ive highlighted the row I need updating

Many Thanks
J




Further-Example.xlsx
Hi All
I modifired it to read this - could you please provide any feedback to confirm if this would work in terms of my additional request above ?

UPDATE [JoJO Maman Bebe Ltd$Attribute Values]
SET [Attribute Values] = CASE
WHEN [Link Field 1] = 'UK2055' THEN [Attribute Value] = 1000
WHEN [Link Field 1] = 'IE3054' THEN [Attribute Value] = 2000
WHEN [Link Field 1] = 'UK2069' THEN [Attribute Value] = 3000
WHEN [Link Field 1] = 'UK2047' THEN [Attribute Value] = 4000
WHEN [Link Field 1] = 'UK2035' THEN [Attribute Value] = 5000
WHEN [Link Field 1] = 'UK2034' THEN [Attribute Value] = 6000
WHEN [Link Field 1] = 'UK2045' THEN [Attribute Value] = 7000
WHEN [Link Field 1] = 'UK2007' THEN [Attribute Value] = 8000
WHEN [Link Field 1] = 'UK2027' THEN [Attribute Value] = 9000
WHEN [Link Field 1] = 'UK2038' THEN [Attribute Value] = 10000
WHEN [Link Field 1] = 'UK2025' THEN [Attribute Value] = 11000
WHEN [Link Field 1] = 'UK2067' THEN [Attribute Value] = 12000
WHEN [Link Field 1] = 'UK2051' THEN [Attribute Value] = 14000
WHEN [Link Field 1] = 'UK2029' THEN [Attribute Value] = 15000
WHEN [Link Field 1] = 'UK2059' THEN [Attribute Value] = 16000
WHEN [Link Field 1] = 'UK2092' THEN [Attribute Value] = 17000
WHEN [Link Field 1] = 'UK2040' THEN [Attribute Value] = 18000
WHEN [Link Field 1] = 'UK2033' THEN [Attribute Value] = 19000
WHEN [Link Field 1] = 'UK2089' THEN [Attribute Value] = 20000
WHEN [Link Field 1] = 'UK2101' THEN [Attribute Value] = 22000
WHEN [Link Field 1] = 'UK2042' THEN [Attribute Value] = 25000
WHEN [Link Field 1] = 'IE3072' THEN [Attribute Value] = 26000
WHEN [Link Field 1] = 'UK2031' THEN [Attribute Value] = 27000
WHEN [Link Field 1] = 'UK2011' THEN [Attribute Value] = 28000
WHEN [Link Field 1] = 'UK2102' THEN [Attribute Value] = 29000
WHEN [Link Field 1] = 'UK2019' THEN [Attribute Value] = 30000
WHEN [Link Field 1] = 'UK2085' THEN [Attribute Value] = 31000
WHEN [Link Field 1] = 'UK2061' THEN [Attribute Value] = 32000
WHEN [Link Field 1] = 'UK2024' THEN [Attribute Value] = 33000
WHEN [Link Field 1] = 'UK2041' THEN [Attribute Value] = 34000
WHEN [Link Field 1] = 'UK2049' THEN [Attribute Value] = 35000
WHEN [Link Field 1] = 'UK2078' THEN [Attribute Value] = 36000
WHEN [Link Field 1] = 'UK2065' THEN [Attribute Value] = 37000
WHEN [Link Field 1] = 'UK2028' THEN [Attribute Value] = 38000
WHEN [Link Field 1] = 'UK2032' THEN [Attribute Value] = 40000
WHEN [Link Field 1] = 'UK2068' THEN [Attribute Value] = 41000
WHEN [Link Field 1] = 'UK2048' THEN [Attribute Value] = 42000
WHEN [Link Field 1] = 'UK2084' THEN [Attribute Value] = 43000
WHEN [Link Field 1] = 'CI3080' THEN [Attribute Value] = 44000
WHEN [Link Field 1] = 'UK2057' THEN [Attribute Value] = 45000
WHEN [Link Field 1] = 'UK2060' THEN [Attribute Value] = 46000
WHEN [Link Field 1] = 'UK2073' THEN [Attribute Value] = 47000
WHEN [Link Field 1] = 'UK2050' THEN [Attribute Value] = 48000
WHEN [Link Field 1] = 'UK2020' THEN [Attribute Value] = 49000
WHEN [Link Field 1] = 'UK2026' THEN [Attribute Value] = 50000
WHEN [Link Field 1] = 'UK2013' THEN [Attribute Value] = 51000
WHEN [Link Field 1] = 'UK2063' THEN [Attribute Value] = 52000
WHEN [Link Field 1] = 'UK2083' THEN [Attribute Value] = 53000
WHEN [Link Field 1] = 'CI3058' THEN [Attribute Value] = 55000
WHEN [Link Field 1] = 'UK2012' THEN [Attribute Value] = 56000
WHEN [Link Field 1] = 'UK2100' THEN [Attribute Value] = 57000
WHEN [Link Field 1] = 'UK2076' THEN [Attribute Value] = 59000
WHEN [Link Field 1] = 'UK2104' THEN [Attribute Value] = 60000
WHEN [Link Field 1] = 'UK2093' THEN [Attribute Value] = 61000
WHEN [Link Field 1] = 'UK2052' THEN [Attribute Value] = 62000
WHEN [Link Field 1] = 'UK2079' THEN [Attribute Value] = 63000
WHEN [Link Field 1] = 'UK2010' THEN [Attribute Value] = 64000
WHEN [Link Field 1] = 'UK2087' THEN [Attribute Value] = 65000
WHEN [Link Field 1] = 'UK2066' THEN [Attribute Value] = 66000
WHEN [Link Field 1] = 'UK2014' THEN [Attribute Value] = 67000
WHEN [Link Field 1] = 'UK2023' THEN [Attribute Value] = 68000
WHEN [Link Field 1] = 'UK2064' THEN [Attribute Value] = 69000
WHEN [Link Field 1] = 'UK2070' THEN [Attribute Value] = 70000
WHEN [Link Field 1] = 'UK2043' THEN [Attribute Value] = 71000
WHEN [Link Field 1] = 'UK2086' THEN [Attribute Value] = 72000
WHEN [Link Field 1] = 'UK2062' THEN [Attribute Value] = 73000
WHEN [Link Field 1] = 'UK2018' THEN [Attribute Value] = 75000
WHEN [Link Field 1] = 'UK2075' THEN [Attribute Value] = 76000
WHEN [Link Field 1] = 'UK2071' THEN [Attribute Value] = 77000
WHEN [Link Field 1] = 'UK2091' THEN [Attribute Value] = 78000
WHEN [Link Field 1] = 'UK2088' THEN [Attribute Value] = 80000
WHEN [Link Field 1] = 'UK2106' THEN [Attribute Value] = 81000
WHEN [Link Field 1] = 'UK2107' THEN [Attribute Value] = 82000
WHEN [Link Field 1] = 'UK2094' THEN [Attribute Value] = 83000
ELSE [Attribute Value]
END
WHERE [Attribute Code] = 'REPLN' and [Link Field 1] IN ('UK2055' , 'IE3054' , 'UK2069' , 'UK2047' , 'UK2035' , 'UK2034' , 'UK2045' , 'UK2007' , 'UK2027' , 'UK2038' , 'UK2025' , 'UK2067' , 'UK2051' , 'UK2029' , 'UK2059' , 'UK2092' , 'UK2040' , 'UK2033' , 'UK2089' , 'UK2101' , 'UK2042' , 'IE3072' , 'UK2031' , 'UK2011' , 'UK2102' , 'UK2019' , 'UK2085' , 'UK2061' , 'UK2024' , 'UK2041' , 'UK2049' , 'UK2078' , 'UK2065' , 'UK2028' , 'UK2032' , 'UK2068' , 'UK2048' , 'UK2084' , 'CI3080' , 'UK2057' , 'UK2060' , 'UK2073' , 'UK2050' , 'UK2020' , 'UK2026' , 'UK2013' , 'UK2063' , 'UK2083' , 'CI3058' , 'UK2012' , 'UK2100' , 'UK2076' , 'UK2104' , 'UK2093' , 'UK2052' , 'UK2079' , 'UK2010' , 'UK2087' , 'UK2066' , 'UK2014' , 'UK2023' , 'UK2064' , 'UK2070' , 'UK2043' , 'UK2086' , 'UK2062' , 'UK2018' , 'UK2075' , 'UK2071' , 'UK2091' , 'UK2088' , 'UK2106' , 'UK2107' , 'UK2094')

THanks
J