Solved

SQL formula update

Posted on 2013-12-04
7
346 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
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 41

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 41

Accepted Solution

by:
pcelba earned 400 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

17 Experts available now in Live!

Get 1:1 Help Now