Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

SQL formula update

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
Keef4000
Asked:
Keef4000
  • 4
  • 2
2 Solutions
 
UnifiedISCommented:
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
 
pcelbaCommented:
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
 
Keef4000Author Commented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Keef4000Author Commented:
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
 
Keef4000Author Commented:
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
 
pcelbaCommented:
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
 
Keef4000Author Commented:
Thank you that worked great. Thanks for staying with it pcelba.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now