Solved

SQL formula update

Posted on 2013-12-04
7
347 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 42

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 42

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
SQL Insert to Begin if data exists 2 31
SQL Query 2 31
Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric. 11 12
In this article I will describe the Copy Database Wizard 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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

808 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