-
Apr 26th, 2021, 08:14 AM
#1
Thread Starter
Member
How do we change our update statment? we want to add rows from NEWschoolmark table th
How do we change our update statment? we want to add rows from NEWschoolmark table that does not existed in schoolmark table
UPDATE schoolmark INNER JOIN NEWschoolmark ON schoolmark.field1 = NEWschoolmark.field1 SET schoolmark.field3 = [NEWschoolmark].field3;
-
Apr 26th, 2021, 08:46 AM
#2
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
You can't update rows that do not exsist in the table already....
You need to do an insert to the table where not already in place...
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 26th, 2021, 10:05 AM
#3
Thread Starter
Member
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
how do i make insert with descreat?
-
Apr 26th, 2021, 10:45 AM
#4
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
We will need more info than you have supplied here... What is the database? Where do the tables exist? Does your DB support the MERGE statement? As say more info
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 27th, 2021, 02:08 AM
#5
Thread Starter
Member
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
ms access 2016 in database yes support merge
-
Apr 27th, 2021, 03:31 AM
#6
Thread Starter
Member
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
what is insert we can make for table schoolmark insert values from Newschoolmark that absent in table schoolmark ?
booth table have the same three fields fields1 fields2 fields3 and key is field1
-
Apr 27th, 2021, 04:16 AM
#7
Thread Starter
Member
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
Why don't work
UPDATE schoolmark RIGHT JOIN NEWschoolmark ON schoolmark.field1 = NEWschoolmark.field1 SET schoolmark.field3 = [NEWschoolmark].field3;
-
Apr 27th, 2021, 07:06 AM
#8
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
If a record exists in table 2 and not in table 1 there is no way to update table 1 it is not there..... If it exists in both table 2 and table 1 then you can update. If it exists in table 1 and not table 2 you can't update either right.....
So
Code:
Update t1 SET
t1.field1 = t2.field1,
t1.field2 = t2.field2,
t1.field3 = t2.field3
FROM table1 t1
INNER JOIN table2 t2
on t1.somefield = t2.somefield;
That updates where the record is in both tables
Now we want to insert the records that exist in table2 but are not in table 1
Code:
INSERT INTO table1 (field1,field2,field3)
Select
field1,
field2,
field3
From table2 t2
WHERE t2.somecomonfield NOT IN (Select somecomonfield from table1)
A merge statement would be used to do all of that in one statement.... I would not have you try that since I believe it is beyond your ability yet (it is also not very efficient most of the time)
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 27th, 2021, 07:20 AM
#9
Thread Starter
Member
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
is it right?
INSERT INTO table1 (field1,field2,field3)
Select
field1,
field2,
field3
From table2 t2
WHERE t2.field1 NOT IN (Select field1 from table1)
key is field1 and consist of unic article number?
what is somecomnfield is it field3 that consist digital value?
table 1
field1 field2 field3
AC3Z2510624A age 6
AC3Z2521410A age 8
AC3Z2521411A age 5,5
AC3Z2552A age 5,5
AC3Z2553A age 5,5
AC3Z25611B08AA age 1,5
AC3Z25611B08AB age 1,5
AC3Z25611B09AA age 1,5
AC3Z25611B09AB age 1,5
table2
field1 field2 field3
AC3Z2521410A age 8
AC3Z2521411A age 5,5
AC3Z2B120A age 8
AC3Z2B120B age 10
AC3Z2B121A age 8
AC3Z2B121B age 10
AE5Z16005A age 6
AE5Z16006A age 6
AE6Z16005A age 6,5
AE6Z16006A age 6,5
-
Apr 27th, 2021, 07:32 AM
#10
Thread Starter
Member
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
why we can not use right join in access like in Oracle finance istead of insert?
UPDATE schoolmark RIGHT JOIN NEWschoolmark ON schoolmark.field1 = NEWschoolmark.field1 SET schoolmark.field3 = [NEWschoolmark].field3;
-
Apr 27th, 2021, 07:46 AM
#11
Thread Starter
Member
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
why does the insert so slow can we remake with visual basic the same?
-
Apr 27th, 2021, 08:12 AM
#12
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
Another way
Code:
INSERT INTO TABLE1 (field1,field2,field3)
SELECT
t2.field1
,t2.field2
,t2.field3
FROM table2 t2
LEFT OUTER JOIN table1 t1
ON t2.field1 = t1.feild1
WHERE t1.field1 IS NULL;
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 27th, 2021, 09:31 AM
#13
Thread Starter
Member
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
where do i read about left outer join
and how do i make it with visual basic for 20000 records very slow
Last edited by daveramsey; Apr 27th, 2021 at 09:40 AM.
-
Apr 27th, 2021, 09:55 AM
#14
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
I have no idea why yours run slowly I can do that over tables with 10,000,000 rows and get good speed.... If you are running MS Access that is not the fastest database there is. The second one down on the left side is what I showed
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 27th, 2021, 11:59 AM
#15
Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl
Originally Posted by daveramsey
where do i read about left outer join
and how do i make it with visual basic for 20000 records very slow
Inner joins return records that exist in both the left and right tables. All other types of joins are considered OUTER... With Left and Right, you just don't need to specify the OUTER as it is implied with the LEFT and RIGHT. The only time OUTER is needed is when using the FULL OUTER construct.
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|