SQL Join with multiple conditions

Question

I need a query to fill the column netto from table test with values from lv from table co when two conditions (test.sp= co.sp) AND (test.ver= co.ver) are given

Table test:

sp	ver	netto
A	X	Null
B	Y	Null
C	Z	Null

Table co:

sp	ver	lv
A	X	F
B	Y	G
C	Z	H

I've tried this query, but it doesn't work.

UPDATE test
SET test.netto= co.lv
FROM test
JOIN co ON (test.sp= co.sp) AND (test.ver= co.ver);

Result should be (table test):

sp	ver	netto
A	X	F
B	Y	G
C	Z	H

I get the following error message:

Error : ERROR: table name specified more than once

Please clarify via edits, not comments. Please read the edit help re block formats for code & quotes & re table format (but that's not appropriate for when a table is initialization code). — philipxy, Jul 26 '22 at 21:20
Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Jul 26 '22 at 21:20
Please before considering posting: Pin down code issues via a [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Jul 26 '22 at 21:21
"What is the dbms" "cut & paste & runnable code & example input" table format (but that's not appropriate for when a table is initialization code" "research" etc etc — philipxy, Jul 26 '22 at 21:28

score 0 · Accepted Answer · answered Jul 26 '22 at 21:47

Your update statement was close.

update test
   set netto = co.lv
  from co
 where test.sp = co.sp
   and test.ver = co.ver

Output of select * from test:

A   X   F
B   Y   G
C   Z

Where test and co were set up as:

create table test (
  sp varchar(10), 
  ver varchar(10), 
  netto varchar(10)
  );

insert into test values 
('A','X',''),
('B','Y',''),
('C','Z','');


create table co (
  sp varchar(10), 
  ver varchar(10), 
  lv varchar(10)
  );

insert into co values 
('A','X','F'),
('B','Y','G'),
('K','Z','H);

SQL Join with multiple conditions

1 Answers1