?
Solved

SQL formula update

Posted on 2013-12-04
7
Medium Priority
?
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 400 total points
ID: 39696645
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 43

Expert Comment

by:pcelba
ID: 39696658
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
ID: 39696722
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:Keef4000
ID: 39696751
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
ID: 39696753
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 43

Accepted Solution

by:
pcelba earned 1600 total points
ID: 39696784
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
ID: 39702205
Thank you that worked great. Thanks for staying with it pcelba.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 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