SQL update from another table

Question

I was wondering if the following was possible:

I have 2 tables.

Table OEORDD
Fields - ORDUNIQ, LINENUM, EXPDATE (among other field but they are not relevant)

Table OEORDHO
Fields - ORDUNIQ, VALUE (among other field but they are not relevant)

Is it possible to have table OEORDD be updated based on Table OEORDHO

I was able to do it when Table OEORDHO only had one field, but I can't seem to do it when it has more.

The data I need is from the following query:

select OPTFIELD,VALUE from OEORDHO where OPTFIELD = 'REQUIREDDATE' and value <> 0

Which needs to update each EXPDATE on table OEORDD matching the VALUE from Table OEORDHO.

Select ORDUNIQ,LINENUM,EXPDATE from OEORDD where ORDUNIQ in ( select ORDUNIQ from OEORDHO where OPTFIELD = 'REQUIREDDATE' and value <> 0)

I tried this, but it returns an error:

update ACCPACAU.dbo.OEORDD set EXPDATE = (select VALUE from OEORDHO where OPTFIELD = 'REQUIREDDATE' and value <> 0) where ORDUNIQ in ( select ORDUNIQ from OEORDHO where OPTFIELD = 'REQUIREDDATE' and value <> 0)

Here is some sample data and what I want it to look like. With that in mind, there will be more and more dates coming, so I need it to be to expand if that makes sense.

Table OEORDHO data:

 +----------+----------+ | ORDUNIQ | VALUE | +----------+----------+ | 21466890 | 20210920 | +----------+----------+ | 21472824 | 20220101 | +----------+----------+

Table OEORDD data:

 +----------+---------+----------+ | ORDUNIQ | LINENUM | EXPDATE | +----------+---------+----------+ | 21466890 | 32 | 20190920 | +----------+---------+----------+ | 21472824 | 32 | 20210716 | +----------+---------+----------+ | 21472824 | 96 | 20210416 | +----------+---------+----------+ | 21472824 | 64 | 20210516 | +----------+---------+----------+

What I need table OEORDD to look like:

 +----------+---------+----------+ | ORDUNIQ | LINENUM | EXPDATE | +----------+---------+----------+ | 21466890 | 32 | 20210920 | +----------+---------+----------+ | 21472824 | 32 | 20220101 | +----------+---------+----------+ | 21472824 | 96 | 20220101 | +----------+---------+----------+ | 21472824 | 64 | 20220101 | +----------+---------+----------+

What is your DBMS? SQL queries should always be tagged with the DBMS you are using. Answers may vary greatly depending on the DBMS. Most DBMS offer some way to update a query, so you don't have to apply the subquery twice. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 16, 2021 at 10:07
do you mean like what i'm using ? if so Microsoft SQL Server Management Studio 2014 — James Smith
– James Smith, Commented Aug 16, 2021 at 10:14
SQL Server Management Studio is a tool to work with SQL SERVER databases. So your DBMS is some version of SQL SERVER. I've tagged your request with SQL Server. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 16, 2021 at 10:18

Thorsten Kettner · Accepted Answer · 2021-08-16 10:25:59Z

1

You simply forgot to correlate the first subquery, so it returns only the one value for the ORDUNIQ the main query is updating:

update ACCPACAU.dbo.OEORDD set EXPDATE = ( select VALUE from OEORDHO where OPTFIELD = 'REQUIREDDATE' and value <> 0 and OEORDHO.ORDUNIQ = OEORDD.ORDUNIQ ) where ORDUNIQ in ( select ORDUNIQ from OEORDHO where OPTFIELD = 'REQUIREDDATE' and value <> 0 );

You can also select the rows with a join and update based on that query result. In SQL Server you can update a query thus:

update oeordd set oeordd.expdate = oeordho.value from accpacau.dbo.oeordd dd join oeordho ho on ho.orduniq = dd.orduniq and ho.optfield = 'REQUIREDDATE' and ho.value <> 0;

edited Aug 16, 2021 at 10:25

answered Aug 16, 2021 at 10:02

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

James Smith Over a year ago

this makes sense but now i get the following error .. Msg 515, Level 16, State 2, Line 22 Cannot insert the value NULL into column 'EXPDATE', table 'OEORDD'; column does not allow nulls. UPDATE fails." I believe this is because only the "ORDUNIQ" in table A have the value as there are MANY ORDUNIQ that don't have the OPTFIELD "REQUIREDDATE"

Thorsten Kettner Over a year ago

That doesn't seem to make sense. In your second subquery you ensure to only update rows where the ORDUNIQ exists in OEORDHO with OPTFIELD = 'REQUIREDDATE' and value <> 0. I don't see how you can get this error. Or, have you removed the where clause from your update statement?

James Smith Over a year ago

yeah im not sure :(

Thorsten Kettner Over a year ago

The statement should work with your original where clause intact. I've added an alternative to this to my answer, where you don't have to apply the same subquery twice.

James Smith Over a year ago

OOOO yes this is magical !! I had to slight modify the code but it worked !! :) set oeordd.expdate = oeordho.value > set OEORDD.expdate = ho.value you are a legend thankyou so much ! update - both answers work <3

Nagib Mahfuz · Accepted Answer · 2021-08-17 04:01:36Z

A simple join and update should do the trick

Update o set o.EXPDATE=oo.value from OEORDD o inner join OEORDHO oo on o.ORDUNIQ=oo.ORDUNIQ

Collectives™ on Stack Overflow

SQL update from another table

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related