Hi
I am a DBA - just been in the job two weeks and I would be grateful for some help.
I am trying to update a table with values from another table.
I have spent two days on this and I think I may have blown a fuse in my brain!
The 'from' table has 3 rows in it and the 'to' table has 3094.
I had problems importing the data in date format from a CSV file, so had to create the 'from' table with the date column as char, otherwise it wouldn't import the date.
There are definitely matching serial numbers in both tables and the from table definitely has a date value in DD/MM/RRRR format in the char column pertaining to the date.
The column in the 'to' table is date format.
This is my script:
update <schema_name>.< totable> s1
set <datecolumn>=
(select TO_DATE(<charco lumn>, 'DD/MM/RRRR')
from fromtable s2
where s1.SERIALNUMBER = s2.SERIALNUMBER );
It says it has updated 3094 rows but when I look at the date column, it has set all the rows to null, includign the 3 I am expecting it to update.
8 2598
Sadly replying to my own post!
Forget the to_date gubbins - it converts it from char automatically with:
update totable a
set (datecolumn) =
(SELECT datecolumn
FROM fromtable B
WHERE A.serialnumber = B.serialnumber) ;
and most saldy that is from a DBA.
Ahem! ;-) You were up at 20 to 6 on a Sunday morning doing this and you call me sad!! I did say I'd only been in the job 2 weeks! ;-p
No not exactly
that was July 28th, 2007,Saturday , 11:12 AM in my time zone.
No not exactly
that was July 28th, 2007,Saturday , 11:12 AM in my time zone.
Ah that's OK then ;-) OK - I appreciate your efforts, really I do, but I still have the problem, in that it should only update 3 rows and is setting the date in all 3097 rows to null. What am I doing wrong?
But you told that is solved , right ......?
No it isn't - I thought you might have misunderstood. I just found that the to_date bit wasn't needed. It still sets 3097 date fields to null where it should be updating only three to values in the other table
I think I need another where clause outside the brackets, like where exists a.serialnumber = b.serialnumber or something? Is that where I'm going wrong?
This is my latest script:
update totable a
set (datefield) =
(SELECT datefield
FROM fromtable B
WHERE A.serialnumber = B.serialnumber)
WHERE exists A.serialnumber = B.serialnumber
In the log, it says:
Data in file has same endianness as the platform - is this a clue?
please post both of your tables structure for my reference.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Dave |
last post by:
I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.
I need to update a field in the NAMEINFO table for a particular surname in a
particular town.
I can select the records fine with this syntax (testing in Oracle SQL* Plus)
SELECT NAMEINFO.LASTNAME, NAMEINFO.FIRSTNAME, NAMEINFO.MIDDLENAME,
NAMEINFO.GENDER, ADDRESSINFO.REGION FROM NAMEINFO, ADDRESSINFO...
|
by: Lauren Quantrell |
last post by:
In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.UniqueID =
tblMyTableTEMP.UniqueID
SET
tblMyTable.myField = tblMyTableTEMP.myField,
tblMyTable.myField2 = tblMyTableTEMP.myField2,
|
by: kalamos |
last post by:
This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)
|
by: serge |
last post by:
/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an Update statement.
I have a sample code to reproduce my problem.
To simplify the scenario I am trying to use Order related
tables to explain a little better the tables i have to work with.
|
by: 001 |
last post by:
Hello,
The select statement needs only 1 second to complete the query.
But the update statement spends 30 minutes. Why?
SELECT STATEMENT:
declare @IDate smalldatetime
select @IDate=col001 from USDay
select * from USDay A
| |
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that there is a threshold on the number of columns of
the table, or perhaps bytes, being updated where the engine just
decides, screw it, i'll just make a new one.
surfed this group and google, but couldn't find anything.
the context: we have some java folk who like to parametize/
|
by: Al |
last post by:
Hi,
I need to update tables in access 97. The table names have spaces (not my
choice). My update fails even though I use the OleDbCommandBuilder.
Here is a code I am using
myDataAdapter = New OleDbDataAdapter
myDataAdapter.SelectCommand = New OleDbCommand("Select * from ", MyConnection)
Dim cb As OleDbCommandBuilder = New
OleDbCommandBuilder(PatientsBillingDataAdapter)
|
by: kdpo |
last post by:
Could someone tell where I can find out if it's true that during
UPDFATE SQL Serve deletes data from table, and then inserts new one.
Thanks
-A
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |