Solved

SQL formula update

Posted on 2013-12-04
7
342 Views
Last Modified: 2013-12-06
I have a table within SQL that I need to update. There is only one column I want to update "USER_4" however I only want it to be updated based on a list of line items from column "CUST_ORDER_ID"

Example:

Currently I have from table "SYSADM_CUSTOMER_ORDER":
 
CUST_ORDER_ID                  USER_4

4902
5220
5564
5936
5952
6079
6083

I want to update it to add values or change exsisting values in the USER_4 colum.

Example:


CUST_ORDER_ID                  USER_4

4902                                      AERO
5220                                      AERO
5564                                      MILT
5936                                      AERO
5952                                      MILT
6079                                      DRIL
6083                                      INDL


I have an excel file that lists the appropriate USER_4 Value based on the CUST_ORDER_ID that has already been defined an updated. However I need this data to be in my SQL table. There are about 3000 lines to be updated.

Is there a way to do this via a command or formula?
0
Comment
Question by:Keef4000
  • 4
  • 2
7 Comments
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 100 total points
Comment Utility
I would start by importing your excel data to a new SQL table--I'll assume it is named table1. In table1, rename the columns by adding "FromExcel" after them.  This will make it easier and more readable.
You will then be able to perform your updates and inserts with sql queries.

UPDATE SYSADM_CUSTOMER_ORDER
SET USER_4 = USER_4FromExcel FROM table1
WHERE CUST_ORDER_ID = CUST_ORDER_IDFromExcel

INSERT INTO SYSADM_CUSTOMER_ORDER (CUST_ORDER_ID, USER_4)
SELECT CUST_ORDER_IDFromExcel, USER_4FromExcel
FROM table1
WHERE CUST_ORDER_IDFromExcel NOT IN (SELECT CUST_ORDER_ID FROM SYSADM_CUSTOMER_ORDER)
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
Because you did not mention it I suppose the Excel file is imported to SQL Server already. You may link the Excel file directly - too difficult maybe - or you have to export text file or CSV from Excel and import it to MS SQL. Everything is possible to do in MS SQL Management Studio.

So I suppose you created a table (YourTempTable) having two columns: ORDER_ID  and USER_4 containing your Excel data.

Use following command to update your existing MS SQL data:

UPDATE sco SET sco.USER_4 = t.USER_4
  FROM  SYSADM_CUSTOMER_ORDER  AS sco  
INNER JOIN YourTempTable t  ON t.ORDER_ID = sco.CUST_ORDER_ID
0
 

Author Comment

by:Keef4000
Comment Utility
pcelba I am getting an error message when I run

UPDATE CUSTOMER_ORDER
SET CUSTOMER_ORDER.USER_4 = table1.USER_4fromexcel
WHERE CUSTOMER_Order.CUSTOMER_ID = table1.CUST_ORDER_Idfromexcel


Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "table1.CUST_ORDER_Idfromexcel" could not be bound.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Keef4000
Comment Utility
UnifiedIS.... I believe I got further following your directions. However I am also getting an error

(0 row(s) affected)
Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'ID', table 'CatBox.dbo.CUSTOMER_ORDER'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
 

Author Comment

by:Keef4000
Comment Utility
UnifiedIS this is what I have with the tables and columns renamed to be what the actually are.

UPDATE CUSTOMER_ORDER
SET USER_4 = USER_4FromExcel FROM table1
WHERE CUSTOMER_ID = CUST_ORDER_IDFromExcel

INSERT INTO CUSTOMER_ORDER (CUSTOMER_ID, USER_4)
SELECT CUST_ORDER_IDFromExcel, USER_4FromExcel
FROM table1
WHERE CUST_ORDER_IDFromExcel NOT IN (SELECT CUSTOMER_ID FROM CUSTOMER_ORDER)
0
 
LVL 41

Accepted Solution

by:
pcelba earned 400 total points
Comment Utility
OK, just remove the whole INSERT INTO command.

You did not ask for new records insertion and exactly this part of the solution generates errors because the INSERT command should generate more columns than expected.

If you need to insert new rows then you have to insert all necessary columns (not nullable ones).
0
 

Author Closing Comment

by:Keef4000
Comment Utility
Thank you that worked great. Thanks for staying with it pcelba.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now