Update A Field Using A W/Join. Please Help.
Nov 21, 2005
I am tryin t to update the tbl1_ID from the tbl2_ID. How do I do a Update Join that will do a comparison on the param column and value columns so that I could get the correct ID into tble 1.
Tbl1 is my destination table and tbl2 is my source. Please Help.
ID Tbl1_ID tb1Param tbl1value
1NULLParam1 0
1NULLParam2 F
1NULLParam3 2
3NULLParam1 0
3NULLParam2 E
3NULLParam3 0
5NULLParam1 0
5NULLParam2 F
5NULLParma3 2
tbl2_ID tbl2Param tbl2value
100param1 0
101param1 1
102param1 2
103param1 3
104param1 4
105param2 E
106param2 F
107param2 H
108param2 HF
109param2 HS
110param2 L
111param2 LS
112param3 0
113param3 1
114param3 2
115param3 3
116param3 4
117param3 5
118param3 6
Here is what Im trying to do if you can understand this.
Update Tbl1
SET tb1ID = B.tbl2_ID
FROM tbl1 AS A JOIN tbl2 AS B
ON A.tbl1Param + A.tbl1Value = B.tbl2Param + A.tbl2value
View 2 Replies
ADVERTISEMENT
Nov 11, 2013
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
View 4 Replies
View Related
Apr 16, 2015
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
View 9 Replies
View Related
Mar 30, 2004
Hi all,
I have a problem about a query to update a table
UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}
where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
View 2 Replies
View Related
May 12, 2006
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View 1 Replies
View Related
Oct 31, 2012
In Access, you have a combo with column designations for example me.combofield.column(x) and you can update another field with those column(x) values.
How do you do it in MS SQL?
I didn't mean "from a 'combo' field'" in SQL(!) I just want to reproduce the equivalent of an Access combo box.
View 14 Replies
View Related
Apr 17, 2008
Hiiiiiiiiiiii,
How to join 2 field to be 1 (alias).
for example, i've 2 field (FirstName & LastName), then i want join thats field to [Name].
How to do that using SQL server 2000
Please advice..
Thanks
View 1 Replies
View Related
Apr 6, 2008
I have APPOINTMENT table with doctor_file_id and patient_file_id as and int ID but bot refer to PERSONS table file_id field
how can i display the file_name from the PERSONS table if both are efering to the same ID?
View 1 Replies
View Related
Sep 19, 2014
I am trying to join two tables together using a particular field. The code to reproduce is:
declare @chris as table
(
chrisid int identity (1,1) primary key,
name varchar(100),
SrvStopID int
)
[Code]...
My result is:
chrisidname SrvStopIDchrisidname SrvStopID
1Neal 1 1John 1
1Neal 1 2Jacob 1
1Neal 1 3Jinglehiemer 1
1Neal 1 4Smith 1
2SmithSon 1 1John 1
2SmithSon 1 2Jacob 1
2SmithSon 1 3Jinglehiemer 1
2SmithSon 1 4Smith 1
and my result should be: Just the results in @Neal that match SrvStopID in @Chris
View 3 Replies
View Related
Oct 25, 2006
Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that?
What I mean is something like:
---------------------------------------------------
Update database.dbo.table set
if(@newvalue !=null)
afield = @newvalue;
-------------------------------------------------
Really need your help on this. Thanks.
View 9 Replies
View Related
Apr 1, 2008
Hello Experts,
Been struggling with this issue all morning and beginning to get a headache. Essentialy my problem is this. I have a gridview control that ive bound to datasource where the select statement is using an inner join to retrieve textual info from one table in refernce to an object_id contained within both. i.e
SelectCommand="SELECT 'multiple fields from both tables'FROM ZCRMTAB_ACTIVITY INNER JOIN ZCRMTAB_ACT_LOG ON ZCRMTAB_ACT_LOG.OBJECT_ID = ZCRMTAB_ACTIVITY.OBJECT_ID WHERE (ZCRMTAB_ACTIVITY.REF_CUST_NO = @REF_CUST_NO) ORDER BY ZCRMTAB_ACTIVITY.CREATED_AT DESC">
<SelectParameters><asp:QueryStringParameter Name="fieldName" QueryStringField="value" Type="String" />
My problem is that this returns individual row results and are displayed on individual lines. Where the object_id is the same i want all the textual info to be displayed within the same row.
Does anyone know how i can accomplish this?
Thanks in advance
View 5 Replies
View Related
May 4, 2006
Is there a way to create one field from multiple records using sql.For exampleTable 1John 18Peter 18David 18Now I want an sql query that when executed will return a field thatlooks like thisQuery1John Peter DavidSo basically it will return one record with all the name in one field
View 4 Replies
View Related
Oct 4, 2006
What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.
I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.
So instead of:
a
122
b
joe
c
st
I would have:
d
122 joe st
Thanks!
View 12 Replies
View Related
Feb 1, 2008
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
View 2 Replies
View Related
Jan 27, 2014
I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.
My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
This is my current query:
SELECT AC.IdActividad, CU.idcuenta
FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad
LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta
As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.
View 3 Replies
View Related
May 10, 2004
i currently have a table like this..
user_id username app_id app2_id app3_id app4_id
1 john 3 4 5 6
2 mike 4 5 6 6
3 manager 4 5 6 6
4 vicepres 5 6 6 6
5 ceo 6 6 6 6
6 board 6 6 6 6
the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..
on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4
any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?
thanks
View 7 Replies
View Related
Jun 30, 2004
Hi all,
Can someone please help me with this update statement?
UPDATE Stats
SET Stats.JobShownInResults = Stats.JobShownInResults + 1
WHERE Jobs.JobID IN
(
SELECT Query that returns IDs from Search criteria
)
From Stats
inner join Jobs
ON Jobs.JobId = Stats.JobID
I'm trying to increment a value in 'Stats' every time a job in 'Jobs' is returned in a search.
Any help much appreciated,
pete
View 5 Replies
View Related
Mar 22, 2013
In the last weeks I came to work with SQL Server more closely and - not being used to it - I stumbled over the sematics of an UPDATE statement using a JOIN (something which is not available in e.g. Oracle).I wonder what the difference between these two updates is:
Code:
update foo
set ..
from bar
where bar.fid = foo.id;
and
Code:
update foo
set ...
from foo f1
join bar on bar.fid = f1.id;
In both cases I have an inner join between foo and bar, but in the second one, foo is actually listed twice in the update statement. As far as I can tell, both carry out the same thing - at least with my test data.
View 10 Replies
View Related
Apr 23, 2008
Best Greetings,
i want to make an update query for a table but the where clause will check a join with other talbe
update table1 inner join table 2 on x.table1=y.table2 and y.table1= y.table2
set z='anything'
or shall it be
update table1
set z='anything'
where x in (select x from table2) and y in (select y from table2)
i dont know how the syntax be any idea plz ,also table 2 it will be derived from table1 in the form of
select x,max(y)
from table1
order by x
View 2 Replies
View Related
Jan 29, 2015
UPDATE sku
set ecomm = 1
from sku
join invt
on sku.style = invt.style
where invt.first_rcvd = '12/22/2014'
I keep getting an error using this query. It keeps failing at the "FROM" portion. What I want to do is update column ECOMM within table SKU if the STYLE has been received on a certain date.
View 4 Replies
View Related
Jun 16, 2006
in a table TBL1 I have to set DESCRIPTION for a TYPE1 equal to DESCRIPTION for a TYPE2 where their ID is equal in TBL1 and their key fields appear together in another table TBL2. In english, in TBL1 the description and id are equal but the type is different. a relationship between their key fields is shown in TBL2. Any thoughts on how to write this?
View 6 Replies
View Related
Jul 6, 2006
I am trying to update a file based on data retrieved from a join and performing a calculation prior to updationg my result field. I end up with "Column qualifier or table B undefined. "
can anyone see what my problem is?
update a
set a.yr2004 = (b.smal + b.smat) * a.qtypre
from commodityf a inner join itmrvb b
on a.i@stid = b.stid
and a.cinbr = b.itnbr
View 7 Replies
View Related
Aug 29, 2007
Here is my update statements which doesn't work, can you show me an example.
UPDATE zurnacik_user SET zurnacik_user.usergroupid=15
INNER JOIN zurnacik_userfield
ON zurnacik_user.userid = zurnacik_userfield.userid
WHERE zurnacik_userfield.field5 = "Kadýn"
AND zurnacik_user.usergroupid = 2
=================
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE zurnacik_userfield.field5 = "Kadýn"
AND zurnacik_user.usergroupid = 2
SE' at line 4
=====
I from Turkey, Thank You...
View 2 Replies
View Related
Jul 23, 2005
Hello,I have two tables (table1 and table2). I want to set a flag in table1 foreach common row with table2. I use the following syntaxUPDATE table1 SET flag='Y' from table1 INNER JOIN table2 on (table1.a =table2.a) AND (table1.b = table2.b)However the situation arises where I may have a row in table2 that matcheswith two or more rows in table1. The requirement is that I only want toset the flag on a single row in table1.
View 3 Replies
View Related
Nov 28, 2006
I am trying to update 1 table with the top records from another table foreach record in the first tableUPDATE HPFSLOWMOVINGSET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOINHPFSLOWMOVING ON HPFSLOWMOVING.ITEMNMBR = IV30300.ITEMNMBR ANDHPFSLOWMOVING.LOCNCODE = IV30300.LOCNCODE WHERE DOCTYPE = 4)This updates all records with the same lastrctdate. I need to update eachrecords with the top lastrctdate where the itemnmbr and locncode equals.Thanks for any help you can provide!Darren
View 4 Replies
View Related
Nov 15, 2006
I have an MS Access query that needs to be converted ot SQL Server 2005
Access Query:
UPDATE tblCheckNumber INNER JOIN tblHistory ON
tblCheckNumber.Autonumber = tblHistory.AutoNumber SET
tblHistory.CheckAmount = ([tblchecknumber].[amount1]), tblHistory.CheckNumber =
[tblchecknumber].[checknumber], tblHistory.CheckDate = [tblchecknumber].[checkdate],
tblHistory.AccountNumber = [tblchecknumber].[AccountNumber],
tblCheckNumber.Updated = "YES"
WHERE
(((tblHistory.CheckAmount) Is Null Or
(tblHistory.CheckAmount)=0) AND ((tblHistory.CheckNumber) Is Null) AND
((tblHistory.CheckDate) Is Null) AND
((tblHistory.AccountNumber) Is Null));
SQL conversion:
UPDATE
tblCheckNumber
SET tblHistory.CheckAmount = ([tblchecknumber].[amount1]), tblHistory.CheckNumber = [tblchecknumber].[checknumber], tblHistory.CheckDate = [tblchecknumber].[checkdate], tblHistory.AccountNumber = [tblchecknumber].[AccountNumber], tblCheckNumber.Updated = "YES"
FROM [DEV_TAXREF].[dbo].tblCheckNumber
INNER JOIN tblHistory
ON tblCheckNumber.Autonumber = tblHistory.AutoNumber
WHERE (((tblHistory.CheckAmount) Is Null Or (tblHistory.CheckAmount)=0) AND ((tblHistory.CheckNumber) Is Null) AND ((tblHistory.CheckDate) Is Null) AND ((tblHistory.AccountNumber) Is Null));
I get the following error:
The multi-part identifier "tblHistory.CheckAmount" could not be bound.
What is wrong?
View 3 Replies
View Related
Jul 16, 2006
I have a datatable : Data_Table and a look up table: Lk_table. Myfield that I
use in Inner Join is defined in both the
data and look table.
So I build my query like this:
SELECT * FROM dbo. Data_Table INNER JOIN
dbo. Lk_table ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield
The pb, sometimes I
have myfield still with its default null value in the datatable: Data_Table.
So, I end up getting 0 record when I execute the query shown above.
How do I turn that around so that even if myfield in Data_Table
is Null, I still get the records from Data_Table. (I don t want a set of
records including all possible values from the look up table: Lk_Table)
View 2 Replies
View Related
Jan 29, 2004
Hello-
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
How do i do this?
View 6 Replies
View Related
Sep 30, 2013
I am trying to join two tables together, on the same field except they have different data types, see the properties below
Code:
TableCOLUMN_NAMEDATA_TYPECHARACTER MAXIMUM LENGTHCHARACTER OCTET LENGTHCHARACTER SET NAMECOLLATION NAME
1itemClassnvarchar 512 1024 UNICODE Latin1_General_CI_AI
2PGCode varchar 3 3 iso_1 Latin1_General_CI_AS
in the code for the join,
Code:
left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))
I have tried using the CAST function on one side of the join then on both, to no avail...
View 6 Replies
View Related
Nov 3, 2007
I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join. With the new, converted field, I do a look up. From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK. This is necessary because SUMDWK is one of the sorted fields. In the look up, it is not possible to change the Output Alias. Does anybody know a way around this? Thanks.
View 14 Replies
View Related
Jun 30, 2015
I have two linked tables from two different databases, there is a column "product" on each table however the product on one table has a Prefix so not a direct match. How can I join these tables ? In the query I have used product2: Replace([scheme_pos.product],"-B","") then tried Joining on product2 but it says JOIN not supported.
View 2 Replies
View Related
May 30, 2007
I need to Update a table with information from another table. Below is my psuedo code - need help with the syntax needed for Sql2000 server.
JOIN tblStateLoc ON tblCompanies.LocationID = tblStateLoc.LocationIDUPDATE tblCompaniesSET tblCompanies.StoreType = tblStateLoc.StoreTypeWHERE tblCompanies.LocationID = tblStateLoc.LocationID
View 2 Replies
View Related
Jan 28, 2008
Hi all,I have a problem with a sql update statement in a store procedure :update table1 set id_cl=t2.id_cl, mail=t2.mail from [table1]t1 right join [table2]t2 on t1.id_cl=t2.id_clWhat I would like is :- Update mail in table 1 from table2 when id_cl are identical -> works- Insert a new id_cl in table1 if it exists in table2 and not in table1 -> doesn't workI thought that the 'right join' would have been able to do that but apparently not.Could you help on this ?Thank you
View 4 Replies
View Related
